Creating a custom Menu in Excel

Ok, so adding a Custom Menu to Excel is a relatively easy affair.. (once you know what you're doing and have code that works well!)

I used to use the following code on Office 2003 (before the ribbon) and it worked just fine. I'm now having to use Office 2007 (at work) and i can confirm this still works and the menu can be found under the Addin's tab (which you might need to enable)

I say might, because i'm not sure. I have the addin's tab enabled all the time as i use it alot.

So, the code. Thanks to Ozgrid.com for the code.

For the workable example paste the following code into "ThisWorkbook" in the VBAProject.

Option Explicit

Private Sub Workbook_Activate()

Run "AddMenus"

End Sub

Private Sub Workbook_Deactivate()

Run "DeleteMenu"

End Sub

In a new module (which i've called) CommandBarMacro paste the following code.

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 Menu Control a caption - i.e New Menu or Daz's Menu or whatever you wish

     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

  '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

 '(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.

     

End Sub

Sub DeleteMenu()

    On Error Resume Next

'Whatever you decided to call your Menu caption (No.5) must be reflected in ("&New Menu")

'For example, lets say we called the Menu Daz's Menu, the code would read

'Application.CommandBars("Worksheet Menu Bar").Controls("Daz's Menu").Delete

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

    On Error GoTo 0

End Sub

In another new Module which i've called OnActionMacros paste the following code.

This module is where all the macro's found on your new menu would be stored.

Option Explicit

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