如何使用 VBA 向单元格添加数据验证 [英] How to add data validation to a cell using VBA

问题描述

我想使用 VBA 在单元格(可变)中添加数据验证"，数据验证列表中的范围也是可变的.到现在我一直在用这个

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

这里的range1"是数据验证列表中的范围，rng"是我想要数据验证的单元格

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

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"

还有谁能给我解释一下

With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="range1"

推荐答案

使用这个:

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

注意，当你使用Dim range1, rng As range时，只有rng有Range的类型，而range1 是 Variant.这就是为什么我使用 Dim range1 As Range, rng As Range.
关于参数的含义，您可以阅读MSDN，但简而言之:

Note that when you're using Dim range1, rng As range, only rng has type of Range, but range1 is 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:

• Type:=xlValidateList 表示验证类型，在这种情况下，您应该从列表中选择值
• AlertStyle:=xlValidAlertStop 指定在验证期间显示的消息框中使用的图标.如果用户从列表中输入任何值，他/她将收到错误消息.
• 在您的原始代码中，Operator:= xlBetween 是奇数.仅当提供两个公式进行验证时才能使用它.
• Formula1:="='" &ws.Name &'！"&用于列表数据验证的 range1.Address 提供带有值的列表地址(格式为 =Sheet!A1:A5)

• Type:=xlValidateList means validation type, in that case you should select value from list
• AlertStyle:=xlValidAlertStop specifies 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:= xlBetween is odd. It can be used only if two formulas are provided for validation.
• Formula1:="='" & ws.Name & "'!" & range1.Address for list data validation provides address of list with values (in format =Sheet!A1:A5)

这篇关于如何使用 VBA 向单元格添加数据验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！