Create menu on the CommandBars

Post date: May 17, 2011 9:15:55 PM

Sub Create_MyMenu()

'Hide the Excel worksheet menu bar

Application.CommandBars(1).Enabled = False

'Remove MyMenu if it exists

On Error Resume Next

MenuBars("MyMenu").Delete

On Error GoTo 0

'Create a new blank menu bar called MyMenu

MenuBars.Add "MyMenu"

'Add menu items "menu", to the MyMenu bar

MenuBars("MyMenu").Menus.Add Caption:="menu"

'Add menu items(macros) under the "menu" menu item.

With MenuBars("MyMenu").Menus("menu").MenuItems

.Add Caption:="&macro1", OnAction:="maacro1"

.Add Caption:="&Macro2", OnAction:="Macro2"

'etc

End With

'Display the "MyMenu" menu bar.

MenuBars("MyMenu").Activate

End Sub

Sub ResetMenus()

'Remove MyMenu if it exists

On Error Resume Next

MenuBars("MyMenu").Delete

On Error GoTo 0

'Display the Excel worksheet menu bar

Application.CommandBars(1).Enabled = True

End Sub