Creating Custom Menus in Excel through VBA

Source :
http://www.ozgrid.com/VBA/custom-menus.htm



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.