使用VBA创建动态验证列表:必需的对象 [英] Creating Dynamic validation list using VBA: Object Required

问题描述

我想通过VBA为动态行数创建数据验证，而每一行都包含动态列数.我传递了变量 out ，该变量指示要在其上设置数据验证的行号， x 是我需要检查验证的最后一列，即将始终以cell(out，2)开头，公式将一直扩展到(out，x).我尝试了以下代码，但它给了我对象必需的错误.我认为我在代码的 Formula 和 SomeNamedRange 部分中犯了一些错误.我应该对代码进行哪些更改以及我在哪里想错了?

I want to create data validation via VBA for dynamic numbers of rows whereas each row is containing dynamic number of columns. I passed the variable out which indicates the row number on which I want to set a data validation, x is the last column up to which I need to check for validation i.e. I will always start with cell(out, 2) and formula will extend up to (out,x). I tried the following code but it's giving me object required error. I think I am making some mistake in Formula and SomeNamedRange sections of the code. What changes should I make in the code and where I am thinking wrong?

Sub DataValidation(out As Integer, x As Integer, y As Integer)
Sheets("first_sheet").Select                          
ActiveSheet.Range(Cells(out, 2), Cells(out, x)).Name = "SomeNamedRange"
Dim RangeToCheck As Excel.Range
Set RangeToCheck = ActiveSheet.Range(Cells(2, 1), Cells(3, 10))
Dim choice
Set choice = "=SomeNamedRange"
With rngRangeToCheck.Select
    Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=choice
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
End Sub

另请参阅所附图片(数据验证应添加到黄色部分).

Also see the attached picture (Data validation should need be added to the yellow portion).

我已按照注释中的建议对代码进行了一些更改，但在 Set choice ="= SomeNamedRange"

I have made some changes to code as suggested in comments but I am still getting the same error in Set choice = "=SomeNamedRange"

更改的代码如下:

Sub DataValidation(out As Integer, x As Integer, y As Integer)
Sheets("first_sheet").Select                            
ActiveSheet.Range(Cells(out, 2), Cells(out, x)).Name = "SomeNamedRange"
Dim RangeToCheck As Excel.Range
Set RangeToCheck = ActiveSheet.Range(Cells(out, 2), Cells(out, x))
Dim choice As String
Set choice = "=SomeNamedRange"
'y is the column number where I want validation i.e. yellow column in picture
With RangeToCheck.Cells(out, y).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=choice
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
End Sub

推荐答案

由于尝试将.Select的返回值用作对象，您会收到一条错误消息.您也没有为 rngRangeToCheck 声明变量，因此未将其设置为任何变量.您需要先选择Range并使用Selection对象，或者直接使用Range:

You get an error because you're trying to use the return value of .Select as an Object. You also don't have a variable declared for rngRangeToCheck, and as such it isn't set to anything. You need to either select the Range first and use the Selection object or just use the Range directly:

With RangeToCheck.Validation
    'Do stuff
    '...
End With   
'Or
RangeToCheck.Select
With Selection.Validation
    'Do stuff
    '...
End With

第二个问题是这些行:

Dim choice
Set choice = "=SomeNamedRange"

您隐式地将"choice"声明为Variant，但是您正在使用Object语法为其分配String.应该是:

You're implicitly declaring 'choice' as a Variant, but you're assigning a String to it using Object syntax. It should be:

Dim choice As String
choice = "=SomeNamedRange"

这篇关于使用VBA创建动态验证列表:必需的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！