“对象不支持该动作”在Excel 2011（Mac OS X）中的下拉菜单 [英] "Object doesn't support this action" for a drop down menu in Excel 2011 (Mac OS X)

问题描述

我有一个使用Office 2010制作的大型Excel工作簿，其中包含一些VBA代码。除了下拉菜单之外，一切似乎都工作得很好。确切地说，它们以图形方式工作，但是

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

抛出对象不支持此操作错误（我确定下拉1是正确的名称，等等），正确地，它被正确地引用（例如 shape = Me.Shapes（1） works），但它似乎不像 ControlFormat 。 Google没有什么帮助任何建议？

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?

我对VBA很新，所以可能会有一些琐碎的调试巫术我不知道。

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

推荐答案

我知道这可以听起来很沮丧和愚蠢的同时，但对于Excel 2011，更改从

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

工作表（Sheet1）。形状（下拉1）。ControlFormat

例如

这将在Excel 2010中工作，但在Excel 2011中不起作用

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

它会给你你的错误

SCREENSHOT

对于Excel 2011，您将不得不使用（完全限定对象

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

注意：替换 Sheet1 与相关工作表名称。

Note: Replace Sheet1 above with the relevant sheet name.

这篇关于“对象不支持该动作”在Excel 2011（Mac OS X）中的下拉菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！