How to replace Excel's standard menu using VBA macros

On this page I show how to replace Excel's standard menu with your own. On the page How to make your own menus in Excel with VBA you can see how to add menus to the standard menu, but in this example we replace the menu completely.

NOTE: In Excel 2007 and newer you don't replace Excel's built-in menu, and I don't know if it is possible anymore. Instead you get a new menu item, "AddIns," and if selected, the whole custom made menu is displayed below the standard menu.

It can be relevant to make your own menu, if for instance you have made an application with many possibilities, or if you want better control of the user's actions.

The special menu will only be active in the special spreadsheet. This is ensured by the two last macros on this page ("Private Sub Workbook_Activate" and "Private Sub Workbook_Deactivate"), which must be inserted in the code sheet for "ThisWorkbook". It is quite easy, and I'll explain how later.

In the example we first replace Excel's standard menu with our own "empty" menu, before we build the menu item by item. As the first menu item to the left we make a "discount" version of Excel's Files menu, where you can open, close, save, print and exit. I have done that to show, how you can use Excel's built-in functionality.

After that we add our own menu items. To get any real action you must write the macros that are called by the different menus - in the example I have just made some message boxes that pop up.

To test the code, highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA-module.

Sub MakeMenu()

'Replaces Excel's standard menu with a user defined menu.
'In Excel 2007 and newer the menu is added to the standard menu.

Dim cbMyBar As CommandBar
Dim cbcMain As CommandBarControl
Dim cbcSub As CommandBarControl

On Error GoTo ErrorHandle

'The menu bar cbMyBar is inserted instead of the standard menu.
Set cbMyBar = CommandBars.Add(Name:="Custom", _
Position:=msoBarTop, MenuBar:=True, Temporary:=True)

With cbMyBar
   .Visible = True
   .Protection = msoBarNoMove
End With

'Now we add the menu items.
'First a "discount" version of the Files menu.

Set cbcMain = cbMyBar.Controls.Add(msoControlPopup, Temporary:=True)
With cbcMain
   .Caption = "&Files"
   .Tag = "Files"
   'TooltipText is the text displayed when
   'the mouse hovers over the menu.
   .TooltipText = "Open, Save, Print and Exit"
End With

'Now we add submenus to the Files menu.
'OnAction tells what macro to run if the menu is selected.
'FaceId = 23 inserts a small file-open icon before the menu.
With cbcMain.Controls.Add(Type:= msoControlButton, Temporary:= True)
   .Caption = "&Open..."
   .OnAction = "FileOpen"
   .Style = msoButtonIconAndCaption
   .FaceId = 23
   .Visible = True
End With

'Next submenu is added: The close command.
With cbcMain.Controls.Add(Type:= msoControlButton, Temporary:= True)
   .Caption = "&Close"
   .OnAction = "CloseBook"
   .Style = msoButtonAutomatic
   .Visible = True
End With

'We make a line above the Save menu:
'.BeginGroup = True
With cbcMain.Controls.Add(Type:= msoControlButton, Temporary:= True)
   .Caption = "&Save"
   .OnAction = "Save"
   .Style = msoButtonIconAndCaption
   .FaceId = 3
   .Visible = True
   .BeginGroup = True
End With

'Save as menu
With cbcMain.Controls.Add(Type:= msoControlButton, Temporary:= True)
   .Caption = "S&ave as..."
   .OnAction = "SaveAs"
   .Style = msoButtonIconAndCaption
   .FaceId = 3
   .Visible = True
End With

'Print dialogue
With cbcMain.Controls.Add(Type:= msoControlButton, Temporary:= True)
   .Caption = "&Print..."
   .OnAction = "PrintBook"
   .Style = msoButtonIconAndCaption
   .FaceId = 4
   .Visible = True
End With

'And finally Exit to stop Excel
With cbcMain.Controls.Add(Type:= msoControlButton, Temporary:= True)
   .Caption = "&Exit"
   .OnAction = "StopProgram"
   .Style = msoButtonAutomatic
   .Visible = True
   .BeginGroup = True
End With

'Now we add a menu to the right of the Files menu.

Set cbcMain = cbMyBar.Controls. _
Add(Type:=msoControlPopup, Temporary:=True)

With cbcMain
   .Caption = "&MyMenu1"
   .TooltipText = "Do this and that"
End With

'Submenu 2.1
Set cbcSub = cbcMain.Controls. _
Add(Type:=msoControlPopup, Temporary:=True)
With cbcSub
   .Caption = "&Lamb chops and more..."
End With

'Makes a submenu to submenu 2.1
With cbcSub.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "Wool..."
   .OnAction = "DummyMacro1"
   .Style = msoButtonIconAndCaption
   .FaceId = 136
End With

'And another submenu to submenu 2.1
With cbcSub.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "B&ones..."
   .OnAction = "DummyMacro2"
   .Style = msoButtonIconAndCaption
   .FaceId = 136
   .Enabled = True
End With

'And another submenu to submenu 2.1
With cbcSub.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "&Horse lasagne..."
   .OnAction = "DummyMacro3"
   .Style = msoButtonIconAndCaption
   .FaceId = 136
End With

'And another submenu to submenu 2.1
With cbcSub.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "R&abbit..."
   .OnAction = "DummyMacro1"
   .Style = msoButtonIconAndCaption
   .FaceId = 136
End With

'Submenu 2.2 to MyMenu1
Set cbcSub = cbcMain.Controls. _
Add(Type:=msoControlPopup, Temporary:=True)

With cbcSub
   .Caption = "&Stock..."
End With

'Submenus to submenu 2.2
With cbcSub.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "&Screws..."
   .OnAction = "DummyMacro1"
   .Style = msoButtonIconAndCaption
   .FaceId = 136
End With

With cbcSub.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "&Nails..."
   .OnAction = "DummyMacro1"
   .Style = msoButtonIconAndCaption
   .FaceId = 136
   .Enabled = True
End With

'Submenu 2.3 to MyMenu1
With cbcMain.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "&Calculate calories"
   .OnAction = "DummyMacro1"
   .Style = msoButtonIconAndCaption
   .FaceId = 133
   .Visible = True
End With

'Submenu 2.4 to MyMenu1
With cbcMain.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "C&alculate weight loss"
   .OnAction = "DummyMacro2"
   .Style = msoButtonIconAndCaption
   .FaceId = 133
   .Visible = True
End With

'The next menu is added to the right of MyMenu1
Set cbcMain = cbMyBar.Controls.Add _
(msoControlPopup, 1, , , True)

With cbcMain
   .Caption = "M&yMenu2"
   .Tag = "MinMenu2"
   .TooltipText = "Heroes && villains"
End With

'Submenu 3.1 is added
With cbcMain.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "&Heroes"
   .OnAction = "DummyMacro2"
   .Style = msoButtonIconAndCaption
   .FaceId = 990
End With

'Submenu 3.2 is added
With cbcMain.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "&Villains"
   .OnAction = "DummyMacro2"
   .Style = msoButtonIconAndCaption
   .FaceId = 990
End With

'Submenu 3.3
With cbcMain.Controls. _
Add(Type:=msoControlButton, Temporary:=True)
   .Caption = "&Save heroe"
   .OnAction = "DummyMacro2"
   .Style = msoButtonIconAndCaption
   .FaceId = 3
End With

BeforeExit:
Set cbcMain = Nothing
Set cbcSub = Nothing
Set cbMyBar = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description & " Error in MakeMenu", vbCritical + vbOKOnly, "Error"
Resume BeforeExit
End Sub

Sub RemoveMenu()
'Removes all menues and toolbars that are not built-in
'and returns to Excel's standard menu.

Dim cbBar As CommandBar

On Error GoTo ErrorHandle

For Each cbBar In Application.CommandBars
   If Not cbBar.BuiltIn Then cbBar.Delete
Next

Exit Sub
ErrorHandle:
MsgBox Err.Description & " RemoveMenu", vbOKOnly, "Error"
End Sub

Here are the macros called by the various menus. You should of course make your own.

Sub FileOpen()
   Application.Dialogs(xlDialogOpen).Show
End Sub

Sub CloseBook()
   ActiveWorkbook.Close
End Sub

Sub Save()
   ActiveWorkbook.Save
End Sub

Sub SaveAs()
   Application.Dialogs(xlDialogSaveAs).Show
End Sub

Sub PrintBook()
   Application.Dialogs(xlDialogPrint).Show
End Sub

Sub StopProgram()
   Application.Quit
End Sub

Sub DummyMacro1()
   MsgBox "Write your own macro.", vbOKOnly, "Hello Dude"
End Sub

Sub DummyMacro2()
   MsgBox "Write some VBA code.", _
   vbOKOnly, "You are under arrest!"
End Sub

Sub DummyMacro3()
   MsgBox "Send more money.", _
   vbOKOnly, "Your daughter"
End Sub

The following two macros must be put in the code sheet for "ThisWorkbook". In the VBA editor you doubleclick the icon "ThisWorkbook" just above the modules in Project Explorer. If it isn't visible press CTRL+R.

The macros will make sure that your menu is only visible, when this spreadsheet is active.

Private Sub Workbook_Activate()
MakeMenu
End Sub

Private Sub Workbook_Deactivate()
RemoveMenu
End Sub

Each menu item has more properties than shown in the example MakeMenu. For instance you can make the menu item visible or invisible by writing .Visible = True or .Visible = False.

If something Then
   .Visible = False
Else
   .Visible = True
end If

There are thousands of the small icons (FaceId), that can be used in menus. At this page is an overview with corresponding numbers.

Replace Excel's standard menu using VBA macros