VBA excel code:cannot give named range to formula1 in validation
14,791
You can use the following code:
'Create the named range (if not done already)
ActiveWorkbook.Names.Add Name:="listdata", RefersTo:= "=Sheet2!$A$1:$A$10"
'Set a validation list on the cells that will refer to the named range
With Range("A1:A100")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=listdata"
End With
End With
Author by
michelle
Updated on June 27, 2022Comments
-
michelle almost 2 years
How can i add a validation list by vba code that will refer to a named range? So that the list will contain the values of a named range? I can do this like Formula1:="=$A$1:$A$10" but how can i give a named range?
-
JMax almost 13 years@Michelle: if this answer fits you, please don't forget to accept it --> meta.stackexchange.com/questions/5234/… and stackoverflow.com/faq for more information