A useful feature of Office Applications like Excel and Word is that you can create your own menu’s and menu items. If you are creating an Add-In you will want to create a menu using VBA.
By doing so you can simply create the menu when your application is started and you can remove the menu again when your application is closed.
The following sub procedure demonstrates how to create a Command Bar and add a few buttons and popups. Both the captions and icons of a button are displayed.
How to Create a Menu with VBA
In VBA a menu is called a Command Bar. It is good practice to include error trapping to avoid error messages for unexpected events like a application crash.
In Excel 2007 the menu’s have been replaced with the ribbon. However, menu’s are still supported in these versions. Once loaded they are located under the tab ‘Add-Ins’.
'Copy the following code to a new module ' Sub CreateMyMenu() Dim myCB As CommandBar Dim myCBtn1 As CommandBarButton Dim myCBtn2 As CommandBarButton Dim myCPup1 As CommandBarPopup Dim myCPup2 As CommandBarPopup Dim myCP1Btn1 As CommandBarButton Dim myCP1Btn2 As CommandBarButton ' Delete the CommandBar if it exists already On Error Resume Next Application.CommandBars("MyMenu").Delete ' Create a new CommandBar Set myCB = CommandBars.Add(Name:="MyMenu", Position:=msoBarFloating) ' Add button 1 to this bar Set myCBtn1 = myCB.Controls.Add(Type:=msoControlButton) With myCBtn1 .Caption = "1st Button" .Style = msoButtonCaption '<- force caption text to show on your button End With ' Add popup menu 1 to this bar - this is a menu that folds out Set myCPup1 = myCB.Controls.Add(Type:=msoControlPopup) myCPup1.Caption = "Statistics" ' Add button 1 to popup menu 1 Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton) With myCP1Btn1 .Style = msoButtonAutomatic .FaceId = 487 End With ' Add button 2 to popup menu 1 Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton) With myCP1Btn1 .Caption = "Click me!" .Style = msoButtonIconAndCaption .FaceId = 59 .OnAction = "SubItworks" '<- call the sub routine SubItWorks End With ' Add a second button to this bar Set myCBtn2 = myCB.Controls.Add(Type:=msoControlButton) With myCBtn2 .FaceId = 17 ' <- Face Id 17 is a barchart icon .Caption = "Barchart" '<- shows when hovering mouse over icon End With ' Show the command bar myCB.Visible = True End Sub Private Sub SubItWorks() MsgBox ("Eureka, it works!") End Sub
Normally you would want to create the menu when the application starts and delete the menu again when the application closes. To make that happen you will need to add another subroutine to the module that will unload the menu.
Sub DeleteMyMenu() On Error Resume Next CommandBars.FindControl(Tag:="MyMenu").Delete CommandBars("MyMenu").Delete End Sub
On the workbook code page you can add the following code to automatically load the menu at startup and unload it on exit.
Private Sub Workbook_BeforeClose(Cancel As Boolean) DeleteMyMenu End Sub Private Sub Workbook_Open() CreateMyMenu End Sub
Courtesy: vba & excel