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

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部