My code is as follows
If Cells(Target.Row, 2) = "" And (Cells(Target.Row, 3) = "" Or Cells(Target.Row, 3) = "") Then
Sheets("MySheet").Activate
Cells(Target.Row, 3).Activate
ActiveCell.Validation.Delete
If (Cells(Target.Row, 2) = "Type A") Then
ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=AvailableVersions"
ElseIf (Cells(Target.Row, 2) = "Type B") Then
ActiveCell.Validation.Delete
Else
ActiveCell.Validation.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertInformation, Formula1:="0", Formula2:="9999999"
End If
End If
So the issue I am having comes whenever I reach ActiveCell.Validation.Add
Run Time Error '1004': Application-defined or object-defined error
Not a very helpful error, and also this occurs with both number and list validation type, so I am sure it is not an issue with the list itself which has workbook level scope anyway. It never occurs on ActiveCell.Validation.Delete which I find weird?
I have been all over google trying to find a solution, and most suggest that it is caused by running dynamic validation code from a button which hogs focus despite the Activate call, but I am running on sheet change event rather than on button press so I don't think this is my issue - any ideas? I've wasted basically a whole day on this! :(