CODEWITHSUNDEEP
excelvba
dritech
dritech

Reputation: 85

Creating Data Validation List using macros

I need to create a drop down menu using macros. In the Internet I found the code below which works perfectly. The only issue is that I need to get the contents on the drop down menu (i.e. =$B$1:$B$6) from a specific sheet (let's say Sheet2). Tried several attempts to direct to a specific sheet, but unfortunately non of them worked.

With Range("A1:A100")
    With .Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$B$1:$B$6"
    End With
End With

Upvotes: 2

Views: 145

Answers (1)

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

Reputation: 10139

Since you are specifying that you want the validation to come from Sheet2, then you need to add that to your formula Formula1:="=Sheet2!$B$1:$B$6".

I would also add a .delete to clear any prior validation first - else you will likely encounter an error if you had any validation from prior attempts.

With Range("A1:A100")
    With .Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=Sheet2!$B$1:$B$6"
    End With
End With

Finally, you should really consider qualifying your Range("A1:A100") with your worksheet, so something like

With Worksheets("Sheet1").Range("A1:A100")

And better yet, consider qualifying the above with your workbook as well.

Upvotes: 3

Related Questions