vba 将单元格数据验证设置为动态范围中的列表

Setting cell data validation as list from dynamic range

提问by baarkerlounger

I'm trying to set cell data validation from a list which is stored in my worksheet using VBA macro. I don't know how long the list will be so the range needs to be dynamically selected.

我正在尝试使用 VBA 宏从存储在我的工作表中的列表中设置单元格数据验证。我不知道列表有多长，因此需要动态选择范围。

At the moment the line .Add Type:= Formula1:="=perfGradeRange" is throwing runtime error '1004' application defined or object defined error.

目前 .Add Type:= Formula1:="=perfGradeRange" 正在抛出运行时错误“1004”应用程序定义或对象定义错误。

My code is this:

我的代码是这样的：

Sub Perf_Grade_Dropdown()

Dim perfGradeData As Worksheet
Dim usedRange As range
Dim rLastCell As range
Dim range As range
Dim perfGradeRange As range

Set perfGradeData = Worksheets("Values")

perfGradeData.Unprotect Password:="pass"

perfGradeData.Activate

Set rLastCell = perfGradeData.Cells.Find(What:="*", After:=Cells(1, 1),    LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

Set perfGradeRange = perfGradeData.range(Cells(1, 1), rLastCell)

Set range = perfGradeData.range(Cells(3, 3), Cells(4, 3))

    With range.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=perfGradeRange"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

perfGradeData.Protect Password:="pass", DrawingObjects:=True, contents:=True,          Scenarios:=True, userinterfaceonly:=True, _
AllowSorting:=True, AllowFiltering:=True, AllowDeletingColumns:=True,    AllowInsertingColumns:=True
perfGradeData.EnableAutoFilter = True

 End Sub

I've seen the following two questions already asked but haven't been able to get any of the suggestions to work:

我已经看到以下两个问题已经被问到，但无法获得任何建议：

Setting validation via VBA in excel fails when using variable

使用变量时，在excel中通过VBA设置验证失败

How do I avoid run-time error when a worksheet is protected in MS-Excel?

在 MS-Excel 中保护工作表时，如何避免运行时错误？

Appreciate any help.

感谢任何帮助。

采纳答案by Santosh

Range is a class so better not to use it for variable name. Secondly Formula1:= needs parameter as a string so i have used perfGradeRange.Cells(1).

Range 是一个类，所以最好不要将它用于变量名。其次 Formula1:= 需要参数作为字符串，所以我使用了perfGradeRange.Cells(1).

Sub Perf_Grade_Dropdown()

        Dim perfGradeData As Worksheet
        Dim usedRange As range
        Dim rLastCell As range
        Dim rng As range
        Dim perfGradeRange As range

        Set perfGradeData = Worksheets("Values")

        perfGradeData.Unprotect Password:="pass"

        perfGradeData.Activate

        Set rLastCell = perfGradeData.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
                                                 xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

        Set perfGradeRange = perfGradeData.range(Cells(1, 1), rLastCell)

        Set rng = perfGradeData.range(Cells(3, 3), Cells(4, 3))

        With rng.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=perfGradeRange.Cells(1)
            .IgnoreBlank = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With


        perfGradeData.Protect Password:="pass", DrawingObjects:=True, contents:=True, Scenarios:=True, userinterfaceonly:=True, _
                              AllowSorting:=True, AllowFiltering:=True, AllowDeletingColumns:=True, AllowInsertingColumns:=True
        perfGradeData.EnableAutoFilter = True

    End Sub