Use this one:
Dim ws As Worksheet Dim range1 As Range, rng As Range 'change Sheet1 to suit Set ws = ThisWorkbook.Worksheets("Sheet1") Set range1 = ws.Range("A1:A5") Set rng = ws.Range("B1") With rng.Validation .Delete 'delete previous validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:="='" & ws.Name & "'!" & range1.Address End With
Note that when you’re using
Dim range1, rng As range, only
rng has type of
Variant. That’s why I’m using
Dim range1 As Range, rng As Range.
About meaning of parameters you can read is MSDN, but in short:
Type:=xlValidateListmeans validation type, in that case you should select value from list
AlertStyle:=xlValidAlertStopspecifies the icon used in message boxes displayed during validation. If user enters any value out of list, he/she would get error message.
- in your original code,
Operator:= xlBetweenis odd. It can be used only if two formulas are provided for validation.
Formula1:="='" & ws.Name & "'!" & range1.Addressfor list data validation provides address of list with values (in format