CommandBars对象,自定义菜单将出现在“加载项”选项卡的“菜单命令”组中;


With Application.CommandBars("worksheet menu bar")
.Reset
Set myBarPopup = .Controls.Add(msoControlPopup)

'CommandBars对象可以使用Add方法新建“加载项”的“自定义工具栏”;

Application.CommandBars("MyToolBar").Delete
Set myBar = Application.CommandBars.Add(MyToolBar)


'自定义菜单命令组
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

'自定义工具栏
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