VBA Validation

[george hart]

Hi all....Thanks in advance...

Is there a way I can change the code below where it reads "xlBetween, Formula1:="=$A$2:$A$21"" to something like Worksheets("Lookup").Range("A1:A50")?

In short I need certain cells validated, but the validation range is on another worksheet which is hidden.

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$2:$A$21"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

 

[Jerry Sullivan]

Hi George,

If you want to use a range on another worksheet as your DV List, you'll need to use a named range.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,  _
    Formula1:="=MyNamedRange"

 

[george hart]

Thanks very much - worked a treat.

Thanks again...