如何使用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 是变式。这就是为什么我使用 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:

• 类型：= 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屋！