Excel dropdowns in VBA: "Unable to get the DropDowns property of the Worksheet class"

Question

I have this code:

Sub addDropdown(Name)
    ActiveSheet.DropDowns.Add(74.25, 60, 188.25, 87.75).Select
    Set n = ActiveSheet.DropDowns(Name)
    If Not (n Is Nothing) Then
        ActiveSheet.DropDowns(Name).Delete
    End If
    With Selection
        .ListFillRange = "$K$15:$M$19"
        .LinkedCell = "$K$8:$L$11"
        .DropDownLines = 6
        .Display3DShading = False
        .Name = Name
    End With
    ActiveSheet.DropDowns(Name).Display3DShading = True
End Sub

Which results in "Runtime error 1004: Unable to get the DropDowns property of the Worksheet class"

I am a VBA noob, so why is it refering to a property? According to the Object Browser DropDowns is a function (although that doesnt rime with the .Add later on).

Also, I can access this exact thing later on after having added something to DropDowns. I just dont get it.

What I want to do, is to delete any pre-existing dropdown with the same name.

Charles Williams Charles Williams · Accepted Answer · 2011-01-08T12:28:15

You need to handle the error if the named Dropdown does not exist

on error resume next
Set n = ActiveSheet.DropDowns(Name)
on error goto 0

Excel dropdowns in VBA: "Unable to get the DropDowns property of the Worksheet class"

1 Answers