完美Excel

CommandBar对象代表三个Excel用户界面元素：

• 自定义工具栏
• 自定义菜单
• 自定义(右键)快捷菜单

在Excel 2007中，CommandBar对象出现在一个相当奇怪的位置。如果编写定制菜单或工具栏的VBA代码，Excel会拦截代码并忽略一些命令，定制的菜单和工具栏将出现在加载项选项卡中的菜单命令或自定义工具栏组中。为便于应用，Excel 2007中的CommandBar对象仅限制在快捷菜单操作中。
本文将提供一些关于CommandBars的背景信息。
CommandBar类型
Excel支持三种类型的CommandBars，通过其Type属性来区分。而Type属性是下面的三个值之一：

• msoBarTypeNormal:工具栏(Type = 0)
• msoBarTypeMenuBar: 菜单栏(Type = 1)
• msoBarTypePopUp: 快捷菜单(Type = 2)

虽然在Excel 2007中没有使用工具栏和菜单栏，但它们仍然被包含于对象模型中，以便于与老版本的Excel相兼容。但是，试图在Excel 2007中显示类型为0或1的命令栏是无效的。例如，在Excel 2003中，下面的语句显示标准工具栏：

但在Excel 2007中，该语句将被忽略。下面专门介绍类型为2的命令栏(快捷菜单)。
列出快捷菜单
Excel 2007有65个快捷菜单，您可以运行下面的ShowShortcutMenuNames过程，遍历命令栏，如果Type属性为msoBarTypePopUp(内置的常量值为2)，则在工作表中显示命令栏的索引值和名称。
Sub ShowShortcutMenuNames()
    Dim Row As Long
    Dim cbar As CommandBar
    Row = 1
    For Each cbar In CommandBars
        If cbar.Type = msoBarTypePopup Then
            Cells(Row, 1) = cbar.Index
            Cells(Row, 2) = cbar.Name
            Row = Row + 1
        End If
    Next cbar
End Sub
图1显示了该过程输出结果的一部分。

图1：一个简单的宏来生成所有快捷菜单的列表
引用命令栏
可以通过Index属性或Name属性引用一个特定的CommandBar对象，例如，下面的两个语句都引用在Excel桌面(Excel中没有打开任何文档时)中单击右键时的快捷菜单：

CommandBars集合是Application对象的一个成员，在标准VBA模块或工作表模块中引用这个集合时，可以忽略对Application对象的引用。例如，下面的语句显示CommandBars集合中索引值为47的对象的名称：

在ThisWorkbook对象代码模块中引用CommandBars集合时，必须在其前面加上Application对象，如：

注意，在不同的Excel版本中，Index值不总是保持不变。
引用命令栏中的控件
一个CommandBar对象包含Control对象，该对象是按钮或菜单。可以通过其Index属性或Caption属性来引用某控件。下面是一个简单的示例过程，显示单元格快捷菜单中第一个菜单项的标题：
Sub ShowCaption()
    MsgBox Application.CommandBars("Cell"). _
       Controls(1).Caption
End Sub

下面的过程显示在工作表选项卡中单击右键时出现的快捷菜单中每个控件的Caption属性(该快捷菜单名为Ply)：
Sub ShowCaptions()
    Dim txt As String
    Dim ctl As CommandBarControl
    For Each ctl In CommandBars("Ply").Controls
        txt = txt & ctl.Caption & vbNewLine
    Next ctl
    MsgBox txt
End Sub

执行该过程后的结果如图2所示。

图2：显示控件的Caption属性
在一些情况下，某快捷菜单中的Control对象包含其它的Control对象。例如，单元格右键菜单中的“筛选”控件包含其它控件，“筛选”控件是一个子菜单，而另外的项目是菜单项。
-----------------------
查找控件
如果所编写的代码将在不同的语言版本的Excel中使用，则应避免使用Caption属性来访问某特定的快捷菜单项。Caption属性随语言的不同而不同，因此在英文版本的Excel中编写的代码可能在其它语言的Excel版本中运行失败。
取而代之的是，应该与控件的ID一起使用FindControl方法(它们将独立于Excel的语言版本)。例如，假设想禁用工作表选项卡快捷菜单中的“重命名”菜单，如果该工作簿仅由Excel英文版本的用户使用，则下面的语句将正常运行：

为了确保该命令在非英语版本的Excel中也能工作，需要知道该控件的ID，上面的语句将告诉您其ID是889：

然后，来禁用该控件，使用下面的语句：

命令栏的名称在各语言版本的Excel中都是一致的，因此，对CommandBars("Desktop")的引用将总会正常运行。
-----------------------
下面的语句显示“筛选”子菜单中的第一个子菜单项：

命令栏控件的属性
命令栏控件有一些属性，来确定控件的外观和状态。下面包含了命令栏控件的一些最常用的属性：

• Caption: 控件中显示的文本。如果该控年仅显示一个图像，则为鼠标悬浮在该控件上时的标题文字。
• ID: 用来识别控件的一个独立的数字。
• FaceID: 代表显示在控件文本旁边的图形图像的数字。
• Type: 一个值，用来确定控件是按钮(msoControlButton)还是子菜单 (msoControlPopup)。
• Picture: 显示在控件文本旁边的图形图像。
• BeginGroup: 值为True时将在控件前面显示一个分隔条。
• OnAction: 用户单击控件时执行的VBA宏名称。
• BuiltIn: 如果该控件是Excel内置控件则为True。
• Enabled: 如果可以单击控件则为True。
• Visible: 如果控件可见则为True。一些快捷菜单包含了隐藏的控件。
• ToolTipText: 用户移动鼠标指针到控件之上时出现的文本(对快捷菜单是不可用的)

显示所有的快捷菜单项
下面的ShowShortcutMenuItems过程创建一个表，列出了每个快捷菜单中的所有第一层次控件。对每个控件，该表包括了快捷菜单的Index和Name以及ID、Caption、Type、Enabled和Visible属性值。
Sub ShowShortcutMenuItems()
    Dim Row As Long
    Dim Cbar As CommandBar
    Dim ctl As CommandBarControl
    Range("A1:G1") = Array("Index", "Name", "ID", "Caption", _
      "Type", "Enabled", "Visible")
    Row = 2
    Application.ScreenUpdating = False
    For Each Cbar In Application.CommandBars
      If Cbar.Type = 2 Then
        For Each ctl In Cbar.Controls
            Cells(Row, 1) = Cbar.Index
            Cells(Row, 2) = Cbar.Name
            Cells(Row, 3) = ctl.ID
            Cells(Row, 4) = ctl.Caption
            If ctl.Type = 1 Then
                Cells(Row, 5) = "Button"
            Else
                Cells(Row, 5) = "Submenu"
            End If
            Cells(Row, 6) = ctl.Enabled
            Cells(Row, 7) = ctl.Visible
           Row = Row + 1
       Next ctl
     End If
    Next Cbar
End Sub
图3显示了过程运行后的一部分结果：

图3：列出所有快捷菜单项
如果运行ShowShortcutMenuItems宏，则会看到有一些快捷菜单包含隐藏或禁用的控件，它们代表在当前上下文环境中不可用的项目。
示例工作簿参见：show shortcut menu items.xlsm。

本文将提供一个样板代码，您可以在需要时对其进行相应的改动并应用。
Excel 2007中老式工具栏的限制
如果想在Excel 2007中创建一个工具栏，应注意下面的一些限制：

• 不能够自由浮动
• 总是显示在加载项选项卡自定义工具栏组中
• Excel会忽略一些CommandBar的属性和方法

创建工具栏的代码
这里的代码假定有一个带有两个名为Macro1和Macro2宏的工作簿，并且在该工作簿打开时创建这个工具栏，在关闭该工作簿时删除这个工具栏。
注意，与Ribbon定制不同，不管当前是哪个工作簿，自定义工具栏者是可见的。
在ThisWorkbook代码模块中，输入下面的过程。第一个过程为在打开工作簿时调用创建工具栏的过程，第二个过程为在关闭工作簿时调用删除工具栏的过程。
Private Sub Workbook_Open()
    Call CreateToolbar
End SubPrivate

Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteToolbar
End Sub 过程CreateToolbar的代码如下:
Const TOOLBARNAME As String = "我的工具栏"
Sub CreateToolbar()
    Dim TBar As CommandBar
    Dim Btn As CommandBarButton
    
    '如果存在则删除已存在的工具栏
    On Error Resume Next
    CommandBars(TOOLBARNAME).Delete
    On Error GoTo 0
    
    '创建工具栏
    Set TBar = CommandBars.Add
    With TBar
        .Name = TOOLBARNAME
        .Visible = True
    End With
    
    '添加按钮
    Set Btn = TBar.Controls.Add(Type:=msoControlButton)
    With Btn
        .FaceId = 300
        .OnAction = "Macro1"
        .Caption = "这里是Macro1的提示"
    End With
    
    '添加另一个按钮
    Set Btn = TBar.Controls.Add(Type:=msoControlButton)
    With Btn
        .FaceId = 25
        .OnAction = "Macro2"
        .Caption = "这里是Macro2的提示"
    End With
End Sub
图1显示了带有这两个按钮的工具栏。

图1：一个老式工具栏，位于加载项选项卡的自定义工具栏中
代码中使用了一个模块级的常量TOOLBARNAME，用来存储工具栏的名称，并用于这两个过程中。
如果已存在具有相同名字的工具栏，则该过程先删除该工具栏。这样将会避免企图创建与已存在的工具栏有相同名称的工具栏时，产生错误。
通过使用CommandBars对象的Add方法来创建该工具栏，使用Controls对象的Add方法来添加两个按钮，每个按钮都有三个属性：

• FaceID:确定显示在按钮中的图像的数字。
• OnAction:在单击按钮时执行的宏。
• Caption:鼠标指针悬浮在按钮上时显示的屏幕提示。

技巧：不仅可以设置FaceID属性，还可以设置Picture属性，使用任何的imageMso图像。例如，下面的语句晶粒示一个绿色的勾号：

关于imageMso图像的更多信息，请参见“定制RibbonX”。
在关闭工作簿时，触发Workbook_BeforeClose事件过程，调用过程DeleteToolbar:
Sub DeleteToolbar()
    On Error Resume Next
    CommandBars(TOOLBARNAME).Delete
    On Error GoTo 0
End Sub

本文参考Ron de Bruin的一系列文章整理
一、创建在所有工作簿中都能使用的自定义菜单
如果想要使自已喜欢的宏在所有工作簿中都可用，则可以复制这些宏到Personal.xlsb工作簿中或者在XLStart目录下使用另一个隐藏的xlsb工作簿，在该文件夹中的每一个文件都在开启Excel时打开。
XLSTART文件夹的位置
C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART
如果找不到指定的文件或文件夹，则可能是Windows设置将其隐藏了，这样需要在文件夹选项中启动“显示所有文件和文件夹”选项。
如何在Excel2007中创建菜单
在Excel 97-2003中，在已存在的菜单栏中创建一个新菜单或者创建自定义菜单栏一点也不困难。但是，在Excel 2007中，定制功能区(Ribbon)并不容易。
1) 在下面的地址中下载文件MyMacroFile.zip：
MyMacroFile.zip
2) 解压并复制该文件到XLSTART文件夹中，然后打开Excel(不能看到该文件，因为它是隐藏的)。
3) 在快速访问工具栏(QAT)中单击鼠标右键，选择“自定义快速访问工具栏”。
在“从下列位置选择命令”下拉框中选择“宏”，然后在“自定义快速访问工具栏”下拉框中选择“用于所有文档(默认)”。
选择“DisplayPopUp”宏，按下“添加” ，然后单击“确定”按扭，如图1所示。

图1
在图1中，可以使用“修改”按钮命令来改变图标。
注 : 仅需执行操作一次，因为该按钮被保存在Excel QAT定制文件中。
如果不想再使用该菜单，则从XLSTART文件夹中移除该xlsb文件后，还需手工从QAT中删除该菜单按钮。
4) 如果在QAT中单击该图标，则将弹出自定义的菜单，如图2所示。

图2
编辑该菜单：
在功能区“视图”选项卡中单击“取消隐藏”命令，在弹出的对话框中选择MyMacroFile.xlsb 文件并单击“确定”按钮。
此时，将显示如图3所示隐藏的工作表“MenuSheet”：

图3
Level: 指定菜单项的层级，有效值为2和3。2级代表菜单项，3级代表子菜单项。
Caption: 显现在菜单、菜单项或子菜单里的文本，使用符号(&)来指定加下划线(热键)的字符。
Macro: 对于2级或3级项目，在选择该项时要执行的宏。如果2级项目有一个或多个3级项，则2级项目可能没有与之相关联的宏。使用Alt+F11键打开VBE编辑器，可以在MacroModule模块中添加或修改宏程序。
Divider: 值为True时，则在菜单项或子菜单项前放置一个分隔条。
FaceID: 可选的。代表显示在项目旁边的内置图形图像的代号数字。
您可以编辑该表中的信息，从而创建自已的菜单。单击“Refresh Menu”按钮来查看是否作出了正确的修改。如果正确，则单击“Hide Save”按钮。
注: 可以下载show faceids.xlsm文件来查找所有的FaceID数字。
二、创建只在一个工作簿中可用的自定义菜单
本节的内容与上节内容大致相同，主要的区别在于工作簿文件为xlsm工作簿，直接打开该工作簿，并不需要将其放置在特定的文件夹中。
下载MyWorkbook.xlsm工作簿文件。
下载该工作簿后，直接在Excel中打开该工作簿，然后按照上节3)以后的内容进行操作即可。
三、在加载项中存储自定义菜单
可以将带有自定义菜单的工作簿保存为Excel加载项(xlam)，然后再在工作簿中启用该加载项。这样，QAT中的按钮将保存在加载项中，并且可以在所有打开的工作簿中使用。
对于上面介绍的示例工作簿，只需将下面的过程中的两行代码删除或注释掉，然后将其保存为Excel加载项。
Sub WBDisplayPopUp()
' If ActiveWorkbook.Name = ThisWorkbook.Name Then
    On Error Resume Next
    Application.CommandBars(ThisWorkbook.Sheets("MenuSheet").Range("B2").Value).ShowPopup
    On Error GoTo 0
' End If
End Sub
创建带有菜单的加载项，而这些菜单中是您想要分发的宏程序。这是一种很好的方式。

提示：本文内容中的所有示例都使用False来禁用命令栏控件，若想启用该控件则将其改为True。
禁用某命令栏
下面的代码将禁用“工作表菜单栏”：
Application.CommandBars("Worksheet Menu Bar").Enabled = False
下面的代码将禁用“标准”工具栏：
Application.CommandBars("Standard").Enabled = False
下面的代码将禁用单元格快捷菜单(即在单元格中右击后出现的菜单)：
Application.CommandBars("Cell").Enabled = False
注意：在代码中，必须使用命令栏的英文名称。如果使用其本地名称，则代码不会运行。
在网上有很多例子，列出了Excel的命令栏名称。您也可以使用下面的代码，列出Excel命令栏的英文名称和本地名称。
Sub Get_Commandbars_Names()
    Dim Cbar As CommandBar
    Dim NewWS As Worksheet
    Dim RNum As Long    RNum = 1
    Set NewWS = Worksheets.Add
    On Error Resume Next
    ActiveSheet.Name = "CommandBarNames"
    On Error GoTo 0    For Each Cbar In Application.CommandBars
        NewWS.Cells(RNum, "A").Value = Cbar.Name
        NewWS.Cells(RNum, "B").Value = Cbar.NameLocal
        RNum = RNum + 1
    Next Cbar    NewWS.Columns.AutoFit
End Sub
禁用所有的命令栏
下面的代码将禁用所有的命令栏：
Sub Disable_Command_Bars_1()
    '禁用所有的命令栏
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        Cbar.Enabled = False
    Next
End Sub
禁用某菜单或控件
如果想要禁用指定的菜单或控件，则必须知道并使用它们的Id。
在下面的地址中可以找到这些资源：
点击下载显示所有图标及ID号的工作簿
点击下载显示命令栏及ID的加载宏
(1)禁用菜单工具栏中的某菜单
下面用两种方式禁用菜单工具栏中的“文件”菜单
1)使用命令栏控件的名称

Application.CommandBars("Worksheet Menu Bar").Controls("文件(F)").Enabled = False
注意，代码中控件的名称应该使用本地名称，比如中文版的Excel则使用"文件(F)"。
2)使用命令栏控件的ID数字(这种方式适用于所有语言的Excel版本)

Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=30002).Enabled = False
由于所使用控件的ID号30002是唯一的，因此可直接使用FindControl方法：
Application.CommandBars.FindControl(ID:=30002).Enabled = False
(2)下面的代码禁用“视图”菜单下的“全屏显示”菜单项：
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=178, Recursive:=True).Enabled = False
代码中，使用Recursive:=True在工作表菜单栏中的所有子菜单中查找。
下面的代码仅禁用“标准”工具栏中的“复制”按钮控件：
Application.CommandBars("Standard").FindControl _
(ID:=19).Enabled = False
下面的代码禁用所有命令栏中的“复制”控件，包括菜单栏、工具栏和快捷菜单。
在Excel 2000-2003版本中，使用下面的代码(使用Office.CommandBarControl)：
Sub MenuControl_False()
' Excel 2000 - 2003
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=19)
        Ctrl.Enabled = False
    Next Ctrl
End Sub
如果要在Excel 97中使用，则使用下面的代码：
Sub MenuControl_Enabled_False()
' Excel 97 - 2003
    Dim Ctl As CommandBarControl
    Dim Cbar As Integer

其他
1) 隐藏公式编辑栏和状态栏
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
2) 禁用快捷键
可以使用Application.OnKey进行处理。例如，要禁用“复制”的快捷键(Ctrl+C)，则使用下面的代码：
Application.OnKey "^c", ""
要恢复快捷键，则使用：
Application.OnKey "^c"
4) 禁用工作表菜单栏和其它工具栏上的右击键
Application.CommandBars("Toolbar List").Enabled = False
5) 阻止用户打开“自定义”对话框
Application.CommandBars.DisableCustomize = True
避免了用户通过右击工具栏或使用“自定义”菜单项打开“自定义”对话框。
6) 禁用工作表菜单栏中“键入需要帮助的问题”：
Application.CommandBars.DisableAskAQuestionDropdown = True
运行代码后，该下拉框将隐藏。
7) 重置命令栏
Application.CommandBars("Worksheet Menu Bar").Reset
重置某内置控件将恢复该控件的原始行为及其属性。重置内置的命令栏将移除自定义控件并恢复内置控件。

使用VBA，可以很方便地向Excel菜单栏添加新菜单。
背景知识
1、菜单栏属于CommandBars集合，可以通过菜单栏名称或索引值对其进行引用：
CommandBars(“Worksheet Menu Bar”)或CommandBars(1)
2、新添加的菜单是一个弹出式控件，其类型为msoControlPopup。
3、通过Add方法向Controls集合中添加新的控件。Add方法可以指定控件的类型、内置控件的ID号、位置及是否是一个临时控件。如果是一个临时控件，则在关闭Excel时会自动删除该菜单。
4、可以指定新菜单的位置。如果不指定，则会在菜单工具栏末尾添加新菜单。
5、使用Caption属性指定新菜单的名称，使用OnAction属性指定单击菜单后的行为。
6、为了避免指定的菜单不存在，可以使用FindControl方法查找要指定的菜单。若指定的菜单不存在，则在工作表菜单栏末尾添加新菜单。
添加菜单
向Excel工作表菜单栏中添加菜单的过程清单如下：
Sub AddNewMenu()
    Dim HelpMenu As CommandBarControl
    Dim NewMenu As CommandBarPopup
    
    '利用ID属性查找帮助菜单
    Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
    
    If HelpMenu Is Nothing Then
        '如果该菜单不存在,则将新菜单添加到末尾
        '设置新菜单为临时的
        Set NewMenu = CommandBars(1).Controls _
          .Add(Type:=msoControlPopup, Temporary:=True)
    Else
        '将新菜单添加到帮助菜单之前
        Set NewMenu = CommandBars(1).Controls _
          .Add(Type:=msoControlPopup, Before:=HelpMenu.Index, _
          Temporary:=True)
    End If
    
    '添加标题并指定快捷键
    NewMenu.Caption = "统计(&S)"
End Sub
运行该过程后，将在工作表菜单的“帮助”菜单左侧添加一个名为“统计”的新菜单。
注：要删除新添加的菜单，使用Delete方法即可，例如代码：

将删除刚创建的“统计”菜单。
添加菜单项
与添加新菜单一样，在添加菜单项及子菜单时，使用Add方法，且指定合适的控件类型，其代码清单如下：
Sub AddNewMenu()
    Dim HelpMenu As CommandBarControl
    Dim NewMenu As CommandBarPopup
    Dim MenuItem As CommandBarControl
    Dim SubMenuItem As CommandBarButton
    
    On Error Resume Next
    '如果菜单已存在,则删除该菜单
    CommandBars(1).Controls("统计(&S)").Delete
    
    '利用ID属性查找帮助菜单
    Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
    
    If HelpMenu Is Nothing Then
        '如果该菜单不存在,则将新菜单添加到末尾
        '设置新菜单为临时的
        Set NewMenu = CommandBars(1).Controls _
          .Add(Type:=msoControlPopup, Temporary:=True)
    Else
        '将新菜单添加到帮助菜单之前
        Set NewMenu = CommandBars(1).Controls _
          .Add(Type:=msoControlPopup, Before:=HelpMenu.Index, _
          Temporary:=True)
    End If
    
    '添加菜单标题并指定热键
    NewMenu.Caption = "统计(&S)"
    
    '添加第一个菜单项
    Set MenuItem = NewMenu.Controls.Add _
      (Type:=msoControlButton)
    With MenuItem
        .Caption = "输入数据(&D)..."
        .FaceId = 162
        .OnAction = "Macro1"
    End With
    
    '添加第二个菜单项
    Set MenuItem = NewMenu.Controls.Add _
      (Type:=msoControlButton)
    With MenuItem
        .Caption = "汇总数据(&T)..."
        '添加快捷键
        .ShortcutText = "Ctrl+Shift+T"
        .FaceId = 590
        .OnAction = "Macro2"
    End With
    
    '添加第三个菜单项
    '本菜单有子菜单项,因此其类型为msoControlPopup
    Set MenuItem = NewMenu.Controls.Add _
      (Type:=msoControlPopup)
    With MenuItem
        .Caption = "数据报表(&R)..."
        '添加分隔线
        .BeginGroup = True
    End With
    
    '添加子菜单
    '添加第一个子菜单
    Set SubMenuItem = MenuItem.Controls.Add _
      (Type:=msoControlButton)
    With SubMenuItem
        .Caption = "月汇总(&M)"
        .FaceId = 110
        .OnAction = "Macro3"
    End With
    
    '添加第二个子菜单
    Set SubMenuItem = MenuItem.Controls.Add _
      (Type:=msoControlButton)
    With SubMenuItem
        .Caption = "季度汇总(&Q)"
        .FaceId = 222
        .OnAction = "Macro4"
    End With
End Sub
注意，菜单类型的不同，有二个菜单项(类型为msoControlButton)、有一个菜单项带有子菜单(其类型为msoControlPopup)
FaceID属性确定出现在菜单文本旁边的图像，以数字表示，一个数字代表一个内置的图像。
在Caption属性中使用&号表示该菜单项的热键。
在第二个菜单项中，利用ShortcutText属性为该菜单项添加了一个快捷键，用户按此快捷键可以直接运行命令。
所添加的菜单如下图所示。

图：添加的新菜单
示例文档在下面的地址中下载：
创建新菜单.rar