How to make your own menus in Excel with VBA

In Excel (and other Office programs) it is pretty easy to make menus tailored to your needs. They are either added to Excel's standard menu, or you can make your own Excel menu. You can also make your own toolbars.

Note: In Excel 2007 and newer you don't replace Excel's built-in menu, and I don't know if it can be done. Instead a new menu item is shown, "AddIns," and if you click this, you see your own menu.

In the example on this page we add a new menu item, "FoodMenu", to the right of the standard Help menu. It is only visible if the particular workbook is active. If you switch to or open another workbook, the menu will not be there.

Each submenu in "FoodMenu" is assigned to a macro (here MyMacro1 og MyMacro2), which is just a messagebox in this example. To test the example, highlight the code with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module. Run the macro "AddMenu".

The two last macros ("Private Sub Workbook_Activate" and "Private Sub Workbook_Deactivate") must be placed in the code sheet for "ThisWorkbook". It is easy and will be explained later.

Option Explicit

Sub AddMenu()
Dim cbMainMenu As CommandBar
Dim cbcMyMenu As CommandBarControl
Dim cbcSubMenu As CommandBarControl

'Step 1
'Delete the menu if it exists.
'If it doesn't exist, it triggers an error,
'and that is why we use On Error Resume Next.

On Error Resume Next

Application.CommandBars("Worksheet Menu Bar"). _
Controls("&FoodMenu").Delete

'Step 2
'Set a CommandBar variable as a Worksheet menu bar

On Error GoTo ErrorHandle
Set cbMainMenu = Application.CommandBars ("Worksheet Menu Bar")

'Step 3
'Add a menu to the main menu.

Set cbcMyMenu = cbMainMenu.Controls.Add (Type:=msoControlPopup)

'Step 4
'Add a caption to the menu

cbcMyMenu.Caption = "&FoodMenu"

'Step 5
'Now we add submenus to our new menu.
'The submenu gets a text and assign a macro
'(OnAction). Here "MyMacro1".

With cbcMyMenu.Controls.Add(Type:=msoControlButton)
   .Caption = "Lamb shank"
   .OnAction = "MyMacro1"
End With

'Step 6
'Adds another submenu

With cbcMyMenu.Controls.Add(Type:=msoControlButton)
   .Caption = "Beef"
   .OnAction = "MyMacro2"
End With

'Repeat step 6 for every submenu you want to add.
'Step 7
'Now we add a submenu that leads to a new submenu
'We give it a new CommandBarControl variable.

Set cbcSubMenu = cbcMyMenu.Controls.Add (Type:=msoControlPopup)

'The submenu gets a caption
cbcSubMenu.Caption = "Ne&xt Menu"

'Step 8
'Now we add the content to the submenu

With cbcSubMenu.Controls.Add(Type:=msoControlButton)
   .Caption = "&Salads"
   'A FaceId assigns a small icon
   .FaceId = 2173
   .OnAction = "MyMacro2"
End With

'Step 9
'For good measure we add another item

With cbcMyMenu.Controls.Add _
(Type:=msoControlButton, Temporary:=True)
   .Caption = "Stews"
   .OnAction = "MyMacro2"
   'BeginGroup inserts a line over the submenu
   .BeginGroup = True
   .FaceId = 1252
End With

BeforeExit:
Set cbcMyMenu = Nothing
Set cbcSubMenu = Nothing
Set cbMainMenu = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description & " Error in AddMenu"
Resume BeforeExit
End Sub

Sub DeleteMenu()
'Deletes the user defined menu

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("FoodMenu").Delete

End Sub

Sub MyMacro1()

MsgBox "Not much meat on this macro", vbInformation, "Lamb shank"

End Sub

Sub MyMacro2()

MsgBox "Not a lot of meat on this one either, huh?", vbInformation, "Menu"

End Sub

Insert the following 2 macros in the code sheet for "ThisWorkbook". In the VBA editor you double click on the icon "ThisWorkbook" just above the module overview in Project Explorer. If it isn't visible, click CTRL+R.

The macros will make your menu visible, when the workbook is active, and invisible when it is not active. If you want the menu to be active on a certain worksheet only, the macros must be in the worksheet's code sheet and not the one for ThisWorkBokk (and then you must replace "Workbook" with "Worksheet").

Private Sub Workbook_Activate()
AddMenu
End Sub

Private Sub Workbook_Deactivate()
DeleteMenu
End Sub

Menu items have more properties than used in the example AddMenu. Besides .OnAction etc. you can for instance make the submenu visible or invisible with the property .Visible = True or .Visible = False.

If this-and-that Then
    .Visible = False
Else
    .Visible = True
end If

Then you must remember to run the macro AddMenu, when and if the condition changes.

There are thousands of the small icons (FaceId). At this page you can see them all with corresponding numbers.