xml - Excel:XML - ContextMenuListRange 的 ContextMenu 编辑

Question

我试图隐藏 <contextMenu idMso="ContextMenuListRange"> 的元素对于 Excel。我的问题是我找不到 child idMso在任何地方。 (甚至在 Office UI 帮助文件中也没有)。

我已经诉诸猜测，但这对我毫无帮助。有人知道我在哪里可以找到这些信息吗？最有帮助的是看到 XML驱动此上下文菜单。

到目前为止我的猜测:

<contextMenus>
   <contextMenu idMso="ContextMenuListRange">
    <button visible = "false" idMso = "Cut"/>
    <button visible = "false" idMso = "Copy"/>
    <button visible = "false" idMso = "InsertComment"/> <!--Not correct-->
    <button visible = "false" idMso = "FormatCells"/> <!--Not correct-->
    <button visible = "false" idMso = "Hyperlink"/> <!--Not correct-->
   </contextMenu>
</contextMenus>

Answer 1

乍一看，我以为我很快就会得到一个 idMSO 列表，但最终并没有那么简单。

手动 RibbonX 设计的文档受到限制 - 因为 Microsoft 推荐的(和 supported )方法是使用 Visual Studio 2017(其中 free 适用于个人开发人员)。

除此之外，最好的办法可能就是坚持使用VBA。

如果您还没有尝试过，还有:

ContextMenus Add-In for Office 2010
A com add-in from Microsoft adds idMso name to the bottom of every Context menu. A simple way to determine the idMso for use it in the RibbonX. (Requires .NET Framework version 3.5)

• Download Microsoft ContextMenusaddin from Ron de Bruin's site here.

• More info including Downloadable Dynamic menu Example files here.

I couldn't get it working since it's specific to 2010 but it sounds promising.

除此之外，最好的办法可能就是坚持使用VBA。

我拼凑了一个 Excel 函数来列出 174 个命令栏中的所有约 1600 个控件 - 但它也可以轻松地修改工具栏。

例如，此语句禁用上下文菜单上的Insert Comments命令:_{(并且=TRUE重新启用)}

Application.CommandBars("Cell").FindControl(ID:=2031).Enabled = False

...以及获取属性值的示例:

Debug.Print Application.CommandBars("Cell").FindControl(ID:=2031).Caption

...或者，相同的结果，但使用 CallByName，因此属性名称可以来自变量(或直接解析):

Debug.Print CallByName(Application.CommandBars("Cell").Controls(cIndex), "Caption", VbGet)

CommandBarControls 本身没有 Name(它们有Captions、accDescription、TooltipText 和 accName 等，但没有常规 Name就像它的 Commandbar 父级一样)，因此引用它们的最佳方式是通过 ID。

<小时/>

示例输出:

   Index      Id   accState   Caption                       Enabled
       1      21    1048576   Cu&t                           TRUE
       2      19    1048576   &Copy                          TRUE
       3      22          1   &Paste                         FALSE
       4   21437          1   Paste &Special...              FALSE
       5    3624          1   &Paste Table                   FALSE
       6   25536    1048576   Smart &Lookup                  TRUE
       7   32736      32768   Tran&slate                     TRUE
       8   32713      32768   &Linked Entity                 TRUE
       9    3181    1048576   &Insert...                     TRUE
      10    3181      32768   &Insert...                     TRUE
      11     292    1048576   &Delete...                     TRUE
      12    3125    1048576   Clear Co&ntents                TRUE
      13   24508    1048576   &Quick Analysis                TRUE
      14   31623      32768   Sp&arklines                    TRUE
      15   31402    1048576   Filt&er                        TRUE
      16   31435    1048576   S&ort                          TRUE
      17    2031    1048576   Insert Co&mment                TRUE
      18    1592      32769   Delete Co&mment                FALSE
      19    1593      32769   Sh&ow/Hide Comments            FALSE
      20     855    1048576   &Format Cells...               TRUE
      21    1966    1048576   Pic&k From Drop-down List...   TRUE
      22    1614      32768   &Show Phonetic Field           TRUE
      23   13380    1048576   Define N&ame...                TRUE
      24    1576      32768   &Hyperlink...                  TRUE
      25    1577      32769   Edit &Hyperlink...             FALSE
      26    1015      32769   &Open Hyperlink                FALSE
      27    3626      32769   &Remove Hyperlink              FALSE

您提到的错误是因为每个 CommandBar 不一定具有每个 Property - 但 Resume Next 可以解决这个问题。由于 Excel 的每个版本都有轻微的 CommandBar 差异，因此最好在您的计算机上生成列表以考虑我们版本之间的差异。

Option Explicit

Sub listContextMenuCommands()

    Const wkshtName = "Commands2"
    Const startRow = 1
    Dim commandbarIndex As Integer
    Dim cIndex As Integer, sht As Worksheet, cb As CommandBar
    Dim cellCmd As Variant, testVar As Variant
    Dim col As Integer, rw As Integer

    'list of selected properties to report upon
    cellCmd = Array("accChild", "accChildCount", "accDefaultAction", "accDescription", "accDoDefaultAction", _
        "accFocus", "accHelp", "accHelpTopic", "accHitTest", "accKeyboardShortcut", "accLocation", "accName", _
        "accNavigate", "accParent", "accRole", "accSelect", "accSelection", "accState", "accValue", "AdaptiveMenu", _
        "AddRef", "BeginGroup", "BuiltIn", "Caption", "Context", "Controls", "Creator", "DescriptionText", _
        "Enabled", "GetIDsOfNames", "GetTypeInfo", "GetTypeInfoCount", "Height", "HelpContextID", "Id", "Index", _
        "InstanceId", "InstanceIdPtr)", "Invoke", "IsPriorityDropped", "Left", "Name", "NameLocal", "OLEUsage", _
        "OnAction", "Parameter", "Parent", "Position", "Priority", "Protection", "QueryInterface", "Release", _
        "RowIndex", "Tag", "TooltipText", "Top", "Type", "Visible", "Width")

    'prepare worksheet for output
    Set sht = Sheets(wkshtName)
    rw = startRow
    If MsgBox("Existing data will be cleared from worksheet '" & wkshtName & "'.", vbOKCancel, "Erase data?") <> vbOK Then Exit Sub
    sht.Cells.ClearContents 'delete all values
    sht.Activate

    'populate headings
    sht.Cells(rw, 1) = "CommandBar"
    sht.Range(Cells(rw, 2), Cells(rw, UBound(cellCmd) + 1)) = cellCmd 'dump array of headings into range
    On Error Resume Next 'errors will be generated for properties unavailable for certain commands

    For commandbarIndex = 1 To Application.CommandBars.Count 'enumerate all command bars

        Set cb = Application.CommandBars(commandbarIndex) ' refer to commandbar by name (like "Cell"=context menu) or by Index #
        For cIndex = 1 To cb.Controls.Count ' enumerate all controls on commandbar
            testVar = CallByName(cb.Controls(cIndex), "Index", VbGet)
            If Err Then 'error: control doesn't exist
                Debug.Print "No cIndex : Commandbar: #" & cb.Index & " '" & cb.Name & "' Ctl# " & cIndex
                Err.Clear 'clear error
                GoTo nextCtl 'skip this command
            End If

            rw = rw + 1 'next row
            sht.Cells(rw, 1) = cb.Name 'title of command bar in first column

            For col = 1 To UBound(cellCmd) 'populate each [col] for this [rw]
                'use "CallByName" so we can specify control name with variables (array)
                sht.Cells(rw, col + 1) = CallByName(cb.Controls(cIndex), cellCmd(col - 1), VbGet)
                If Err Then 'catch error
                    sht.Cells(rw, col + 1) = "{N/A}" 'property not available for this control
                    Err.Clear 'clear error
                End If

            Next col 'next column
            Application.StatusBar = "Listing Commands: " & Format(commandbarIndex / Application.CommandBars.Count, "0.0%")

nextCtl:
        Next cIndex  'next control
        DoEvents
    Next commandbarIndex 'next commandbar

    sht.[a1].Select
    sht.Columns.AutoFit

    Debug.Print "Finished " & Application.CommandBars.Count & " Command Bars, " & rw - startRow & " commands."
End Sub

该代码有点困惑且效率低下，因此在获取约 100k 属性时可能会挂起一会儿。您只需在常量中指定一个空白工作表名称，然后您就有希望获得相关命令栏信息的完整列表。

<小时/>

来自 MSDN 的更多信息:

• CommandBar Properties (Office)

• CommandBars Members (Office)

• Customizing Context Menus in All Versions of Microsoft Excel

• Customizing the 2007 Office Fluent Ribbon for Developers (Part 2/3)

• CommandBars.FindControl Method (Office)

• CallByName Function