Using a Variable in Data Validation

[music_al]

Hi

I am passing a string to a string variable (which is correctly declared as a STRING) and the strings I am passing are the names of ranges in a worksheet. I would like to use the variable name in the data validation method...

        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:= VARIABLE NAME WOULD GO HERE
            .IgnoreBlank = True
            .InCellDropdown = True
        End With

I have tried several variations on this; putting it in speech marks, brackets, semicolon etc, but I just cant get it to work. Any help would be greatly appreciated.

 

[RoryA]

You need an equals sign, so:

Formula1:="=" & variableName

 

[Peter_SSs]

When you want to highlight part of your vba code, use the RICH code tags, not the VBA code tags. I have changed that for you above

Try

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="=" & StrVariable
    .IgnoreBlank = True
    .InCellDropdown = True
End With

 

[music_al]

Thank you guys. That is very much appreciated

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)