“Object doesn't support this action” for a dro

问题:

I have a big Excel Workbook made with Office 2010 with some VBA code. Everything seems to work fine apart the drop down menus. Precisely, they work, graphically, but

Me.Shapes("Drop Down 1").ControlFormat

throws an "Object doesn't support this action" error (I am sure that "Drop Down 1" is the correct name, etc.), precisely, it gets referenced correctly (e.g. shape = Me.Shapes(1) works) but it doesn't seem to like ControlFormat. Google doesn't help much; any suggestions?

I'm quite new to VBA so there might be some trivial debugging witchcraft I'm not aware of.

EDIT: I tried creating a new workbook with a dummy dropdown menu and selecting the values whilst recording a macro but it gives no result (it's like the menu never existed).

回答1:

I know this can sound frustrating and Stupid at the same time but for Excel 2011, change the line from

Me.Shapes("Drop Down 1").ControlFormat

to

Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat

For example

This will work in Excel 2010 but not in Excel 2011

Sub Sample()
    With Me.Shapes("Drop Down 1").ControlFormat
        .AddItem "Sid"
    End With
End Sub

It will give you the error that you mentioned.

SCREENSHOT

For Excel 2011, you will have to use (Fully qualify the object)

Sub Sample()
    With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat
        .AddItem "Sid"
    End With
End Sub

SCREENSHOT

Note: Replace Sheet1 above with the relevant sheet name.