Setting cell data validation as list from dynamic range

Question

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.

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

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

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

Appreciate any help.

Santosh Santosh · Accepted Answer · 2013-07-10T03:27:35

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).

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

Setting cell data validation as list from dynamic range

1 Answers