vba实现展开菜单（自定义菜单命令组）

VBA中自定义菜单命令组、自定义工具栏主要是利用CommandBars对象的方法进行添加，添加的自定义菜单命令组和自定义工具栏将出现在“加载项”选项卡中。

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

,