VBA excel code:cannot give named range to formula1 in validation

3
votes

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?

1
validationvbaexcel

1 Answers

13
votes

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