Create Custom Menu Items in Excel VBA
Custom Menu Items Created on the fly With Excel VBA
Got any Excel Questions? Free Excel Help Adding Custom Menu Item in Excel See
It is possible to customize Excel in many different ways. However, to make your Excel spreadsheets have that professional look and feel, one can add their own custom menus to an existing Excel toolbar. This is best done via VBA and the same menu items you add, should be deleted upon deactivating/closing the Excel Workbook.
Sample Code to Add/Delete Custom Menu Items
You can simply copy the code below into any standard Excel Module
Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
'(1)Delete any existing one. We must use On Error Resume next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0
'(2)Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")
'(3)Return the Index number of the Help menu. We can then use _
this to place a custom menu before.
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index
'(4)Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)
'(5)Give the control a caption
cbcCutomMenu.Caption = "&New Menu"
'(6)Working with our new Control, add a sub control and _
give it a Caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 1"
.OnAction = "MyMacro1"
End With
'(6a)Add another sub control give it a Caption _
and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
.OnAction = "MyMacro2"
End With
'Repeat step "6a" for each menu item you want to add.
'Add another menu that will lead off to another menu
'Set a CommandBarControl variable to it
Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
' Give the control a caption
cbcCutomMenu.Caption = "Ne&xt Menu"
'Add a contol to the sub menu, just created above
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Charts"
.FaceId = 420
.OnAction = "MyMacro2"
End With
End Sub
Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0
End Sub
Sub MyMacro1()
MsgBox "I don't do much yet, do I?", vbInformation, "Ozgrid.com"
End Sub
Sub MyMacro2()
MsgBox "I don't do much yet either, do I?", vbInformation, "Ozgrid.com"
End Sub
Code to Fire off the Above Code
The code here must be placed in the Private Module of the Worksheet Object (ThisWorkbook). To get there from within Excel proper, right click on the Excel icon, top left next to "File", and click "View Code". In here paste the code below;
Private Sub Workbook_Activate() Run "AddMenus" End Sub Private Sub Workbook_Deactivate() Run "DeleteMenu" End Sub
Now save the Workbook and open any other. You will note that as you go from one Workbook to another the custom menu items are deleted. As soon as the Workbook is activated again, the custom menu items are re-created. The same thing happens when closing/opening the Workbook. Download Working example
See also:
Free Training Course: Lesson 1 - Excel Fundamentals
See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
