CODEWITHSUNDEEP
excelvba
efoc
efoc

Reputation: 641

VBA Macro: Validation not recognized

I'm trying to create a dropdown list but the error 1004 'Application-defined or object-defined error' comes up at the .add function all the way to Formula1.

I have tried:

I have no idea what is wrong. I think it might not be recognized but I am unsure.

Sheets("Sheet1").Range("C2").Offset(0, 3).Select
MsgBox cell

With Selection.Validation.Add(Type:=xlValidateList, _
                              AlertStyle:=xlValidAlertStop, _
                              Operator:=xlBetween, _
                              Formula1:="B2:B5")
    .IgnoreBlank = True
    .InCellDropdown = True
End With

Upvotes: 1

Views: 540

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

Don't use .Add in your With Block.

Sheets("Sheet1").Range("C2").Offset(0, 3).Select

With Selection.Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$B$2:$B$5"
    .IgnoreBlank = True
    .InCellDropdown = True
End With

And I would suggest avoiding .Select and declare your range variables.

Dim myRng As Range
Set myRng = ThisWorkbook.Worksheets("Sheet1").Range("F2")

With myRng.Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$B$2:$B$5"
    .IgnoreBlank = True
    .InCellDropdown = True
End With

It probably wouldn't be a bad idea to ensure that you don't currently have validation set there using .delete - it may throw an error if you already have data validation set from a prior attempt.

With myRng.Validation
    .delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$B$2:$B$5"
    .IgnoreBlank = True
    .InCellDropdown = True
End With

Upvotes: 2

Related Questions