如何使用 VBA 向单元格添加数据验证
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22956604/
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
How to add data validation to a cell using VBA
提问by Gajju
I want to add "data validation" in a cell(which is variable) using VBA and the range which is to come in the data validation list is also variable. Till now I have been using this
我想使用 VBA 在单元格（这是可变的）中添加“数据验证”，并且数据验证列表中的范围也是可变的。直到现在我一直在使用这个
Here "range1" is the range which is to come in the data validation list and "rng" is the cell where I want the data validation
这里“range1”是数据验证列表中的范围，“rng”是我想要数据验证的单元格
Dim range1, rng As range
Set range1 = range("a1:a5")
Set rng = range("b1")
With rng
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="range1"
End With
End With
I am getting "application defined and object defined error"
我收到“应用程序定义和对象定义错误”
Also can someone explain me the meaning of different arguments in
也有人可以向我解释不同论点的含义
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="range1"
回答by Dmitry Pavliv
Use this one:
使用这个：
Dim ws As Worksheet
Dim range1 As Range, rng As Range
'change Sheet1 to suit
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set range1 = ws.Range("A1:A5")
Set rng = ws.Range("B1")
With rng.Validation
.Delete 'delete previous validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="='" & ws.Name & "'!" & range1.Address
End With
Note that when you're using Dim range1, rng As range, only rnghas type of Range, but range1is Variant. That's why I'm using Dim range1 As Range, rng As Range.
About meaning of parameters you can read is MSDN, but in short:
请注意，当您使用 时Dim range1, rng As range，只有rng类型为Range，但类型range1为Variant。这就是我使用Dim range1 As Range, rng As Range.
关于您可以阅读的参数含义是MSDN，但简而言之：
Type:=xlValidateListmeans validation type, in that case you should select value from listAlertStyle:=xlValidAlertStopspecifies the icon used in message boxes displayed during validation. If user enters any value out of list, he/she would get error message.- in your original code,
Operator:= xlBetweenis odd. It can be used only if two formulas are provided for validation. Formula1:="='" & ws.Name & "'!" & range1.Addressfor list data validation provides address of list with values (in format=Sheet!A1:A5)
Type:=xlValidateList表示验证类型，在这种情况下，您应该从列表中选择值AlertStyle:=xlValidAlertStop指定在验证期间显示的消息框中使用的图标。如果用户从列表中输入任何值，他/她将收到错误消息。- 在您的原始代码中，
Operator:= xlBetween很奇怪。仅当提供两个公式进行验证时才能使用它。 Formula1:="='" & ws.Name & "'!" & range1.Address用于列表数据验证提供带有值的列表地址（格式=Sheet!A1:A5）
