细品RibbonX(46)：在Excel 2007的QAT中以表驱动的方式构建自定义菜单

一、创建在所有工作簿中都能使用的自定义菜单
如果想在所有工作簿中都可以使用自已喜欢的宏，那么可以将这些宏复制到Personal.xlsb工作簿中，或者在XLStart文件夹中使用另一个隐藏的xlsb工作簿（在Excel启动时会打开该文件夹中的每一个文件），也可以创建加载项。
XLSTART文件夹的位置

C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\XLSTART

如果找不到指定的文件或文件夹，则可能是Windows设置将其隐藏了，此时需要在文件夹选项中启动“显示所有文件和文件夹”选项。
如何在Excel2007中创建菜单
在Excel 97-2003中，在已存在的菜单栏中创建一个新菜单或者创建自定义菜单栏一点也不困难。但是在Excel 2007中，定制功能区并不容易。
1) 在下面的地址中下载文件MyMacroFile.zip：
http://www./files/My%20Add-in.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 name: 对于2级或3级项目，在选择该项时要执行的宏。如果2级项目有一个或多个3级项，则2级项目可能没有与之相关联的宏。使用Alt+F11键打开VBE编辑器，可以在MacroModule模块中添加或修改宏程序。
Divider: 值为True时，则在菜单项或子菜单项前放置一个分隔条。
FaceID: 可选的。代表显示在项目旁边的内置图形图像的代号数字。
您可以编辑该表中的信息，从而创建自已的菜单。单击“Refresh Menu”按钮来查看是否作出了正确的修改。如果正确，则单击“Hide Save”按钮。
二、创建只在一个工作簿中可用的自定义菜单
本节的内容与上节内容大致相同，主要的区别在于工作簿文件为xlsm工作簿，直接打开该工作簿，并不需要将其放置在特定的文件夹中。
在下面的地址中下载MyWorkbook.xlsm工作簿文件。
http://www./files/MyWorkbook.zip
下载该工作簿后，直接在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

创建带有菜单的加载项，而这些菜单中是您想要分发的宏程序。这是一种很好的方式。

注：本文参考了Ron de Bruin的一系列文章，有兴趣的朋友可以直接参考其网站的文章。
同时，参见：表驱动的方式构建自定义菜单

表驱动的方式构建自定义命令栏(示例2)

下面是John Walkenbach介绍的创建自定义菜单的技巧，辑录于此。
Excel 97-2003使用CommandBars对象来构建菜单，必须使用VBA创建特定工作簿的菜单，下面的技巧介绍了一种创建自定义菜单的相对简单的方法。当特定的工作簿打开时，出现自定义菜单；当关闭该工作簿时，删除自定义菜单。
示例中，包含了用于创建自定义菜单的所有VBA代码，在大多数情况下，不需要修改这些代码，而只需要简单地修改MenuSheet工作表。
注意，这里介绍的技术只是新建菜单，而不会在已有的菜单中添加菜单项。
MenuSheet工作表
这项技术使用了一个Excel工作表，这里将其命名为MenuSheet。要创建自定义菜单，只需简单地修改表中的数据。MenuSheet工作表如下图所示。

工作表MenuSheet包含5列：

• 菜单层级：特定项目的层级。有效值为1、2、3。层级1代表菜单；2代表菜单项；3代表子菜单项。通常，有一个层级1的项目，在它下面是层级2的项目。层级2的项目可以有也可以没有层级3的项目（子菜单）。
• 标题：菜单、菜单项或子菜单显示的文本。使用符号（&）指定带下划线的字符，即快捷键。
• 位置/宏：对于层级1的项目，应该是一个表示在菜单栏中位置的整数。对于层级2或层级3的项目，应该是选择该项目时执行的宏的名称。如果层级2的项目有一个或多个层级3的项目，那么层级2的项目可能没有与之相关的宏。
• 分隔线：如果为True，则在该菜单项或子菜单项之前应该放置一条分隔线。
• FaceID：可选的。代表显示在菜单项旁边的内置图像的编号。

菜单示例
使用上图所示表所创建的菜单如下图：

代码清单

Sub CreateMenu()
'   打开工作簿时执行本过程
'   注: 在本过程中没有编写错误处理的代码

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup
 
Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId
 
''''''''''''''''''''''''''''''''''''''''''''''''''''
'   菜单数据所在的工作表
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

'   确保不会出现重复菜单
    Call DeleteMenu
 
'   初始化行计数器
    Row = 2
 
'   使用存储在MenuSheet工作表中的数据添加菜单,菜单项和子菜单项
    Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With
 
Select Case MenuLevel
Case 1 ' 代表菜单
'              在工作表命令栏中添加顶级菜单
                Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption
 
Case 2 ' 代表菜单项
                If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> "" Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True
 
Case 3 ' 代表子菜单项
                Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> "" Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub
 
Sub DeleteMenu()
'   关闭工作簿时执行本过程
'   删除创建的菜单
    Dim MenuSheet As Worksheet
Dim Row As Integer
Dim Caption As String
 
On Error Resume Next
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
Row = 2
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
If MenuSheet.Cells(Row, 1) = 1 Then
Caption = MenuSheet.Cells(Row, 2)
Application.CommandBars(1).Controls(Caption).Delete
End If
Row = Row + 1
Loop
On Error GoTo 0
End Sub
 
Sub DummyMacro()
MsgBox "这里一个用于演示的宏."
End Sub

代码简要解释

• CommandBars(1)引用“工作表菜单栏”，也可以通过名称“Worksheet Menu Bar”来引用，其中1代表“工作表菜单栏”在CommandBars集合中的索引。
• CommandBars集合是所有CommandBar对象的集合，而每个CommandBar对象都有一个 Controls集合。
• Add方法向Controls集合中添加新控件。参数Type为msoControlPopup时，指定控件类型为弹出式控件；参数Before指定所添加的控件的位置；参数Temporary设置为True，表示为临时命令栏。
• FaceID属性确写出现在菜单文本旁的图像，其中的数字代表内置图像编号。
• BeginGroup属性设置为True时，将在该菜单项前放置分隔条。

技术运用
按下列步骤在工作簿或加载项中使用这项技术：
1、在VBE中插入一个标准模块，将上述代码复制到该模块中。
2、在ThisWorkbook模块中，编写下列代码：

Private Sub Workbook_Open()
Call CreateMenu
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub

在工作簿打开时执行Workbook_Open事件，在工作簿关闭时执行Workbook_BeforeClose事件。
3、在工作簿中插入一个新工作表并命名为MenuSheet。按上图所示的格式输入菜单数据，或者直接复制上述数据后，再进行修改。