VBA excel代码：在验证中不能给命名范围到formula1 [英] VBA excel code:cannot give named range to formula1 in validation

问题描述

如何通过vba代码添加一个可以引用命名范围的验证列表？
列表将包含命名范围的值？
我可以像Formula1这样做：== $ A $ 1：$ A $ 10，但是如何给出命名范围？

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?

推荐答案

您可以使用以下代码：

'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

这篇关于VBA excel代码：在验证中不能给命名范围到formula1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！