validation - VBA Excel代码:cannot give named range to formula1 in validation

Question

如何通过 VBA 代码添加引用命名范围的验证列表？这样列表将包含命名范围的值？我可以像 Formula1:="=$A$1:$A$10"那样执行此操作，但如何给出命名范围？

Answer 1

您可以使用以下代码:

'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