VBA中自定义菜单命令组、自定义工具栏主要是利用CommandBars对象的方法进行添加，添加的自定义菜单命令组和自定义工具栏将出现在“加载项”选项卡中。
1 自定义菜单命令组
Sub AddCommandbars()
Dim myBarPopup As CommandBarPopup
Dim ArrOne As Variant
Dim ArrTwo As Variant
Dim ArrThree As Variant
Dim i As Byte
On Error Resume Next
' 定义各命令按钮的标题；
ArrOne = Array("多工作簿查找", "创建工作表目录", "设置页眉页脚")
' 定义各命令按钮的图标ID；
ArrTwo = Array(281, 283, 285)
ArrThree = Array("FormOpen", "PERSONAL.XLSB!创建工作表目录", "PERSONAL.XLSB!设置页眉页脚")
' CommandBars对象可以使用.controls.Add方法新建“加载项”的“菜单命名”组；
With Application.CommandBars("worksheet menu bar")
' 重置内置命令栏，避免重复添加；
.Reset
' 声明弹出式控件
Set myBarPopup = .Controls.Add(msoControlPopup)
With myBarPopup
' 设置命令栏控件的标题
.Caption = "controls"
For i = 0 To UBound(ArrOne)
With .Controls.Add(msoControlButton)
.Caption = ArrOne(i)
.FaceId = ArrTwo(i) '控件图标指定；
.OnAction = ArrThree(i)
End With
Next
End With
End With
Set myBarPopup = Nothing
End Sub
2 自定义工具栏
Sub AddBars()
Dim myBar As CommandBar
Dim ArrOne As Variant
Dim ArrTwo As Variant
Dim ArrThree As Variant
Dim i As Byte
On Error Resume Next
ArrOne = Array("多工作簿查找", "创建工作表目录", "设置页眉页脚")
ArrTwo = Array(9893, 284, 9590)
ArrThree = Array("FormOpen", "PERSONAL.XLSB!创建工作表目录", "PERSONAL.XLSB!设置页眉页脚")
Application.CommandBars("MyToolBar").Delete
' CommandBars对象可以使用Add方法新建“加载项”的“自定义工具栏”；
Set myBar = Application.CommandBars.Add(MyToolBar)
With myBar
.Visible = True
For i = 0 To UBound(ArrOne)
With .Controls.Add(msoControlButton)
.Caption = ArrOne(i)
.FaceId = ArrTwo(i)
.OnAction = ArrThree(i)
.Style = msoButtonIconAdnCaptionBelow
End With
Next
End With
Set myBar = Nothing
End Sub
3 工作薄打开时自动执行定义的过程
Private Sub Workbook_Open()
frmFind.Show
Call AddCommandbars
Call AddBars
End Sub
效果图
