VBA excel代码：无法在验证中为formula1提供命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6786540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-11 13:41:19 来源:igfitidea点击:
VBA excel code:cannot give named range to formula1 in validation
提问by michelle
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?
如何通过将引用命名范围的 vba 代码添加验证列表？这样列表将包含命名范围的值？我可以像 Formula1:="=$A$1:$A$10" 那样做，但是我怎么能给出一个命名范围呢？
回答by JMax
You can use the following code:
您可以使用以下代码：
'Create the named range (if not done already)
ActiveWorkbook.Names.Add Name:="listdata", RefersTo:= "=Sheet2!$A:$A"
'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
