Custom menus in Excel97 and later

In Excel97 and later, menus and toolbarbuttons can be manipulated with the CommandBar-object. Here are some example macros that will help you get started with the creation of your own menus:

Sub CreateMenu()
' creates a new menu.
' can also be used to create commandbarbuttons
' may be automatically executed from an Auto_Open macro or a Workbook_Open eventmacro
Dim cbMenu As CommandBarControl, cbSubMenu As CommandBarControl
    RemoveMenu ' delete the menu if it already exists
    ' create a new menu on an existing commandbar (the next 6 lines)
    Set cbMenu = Application.CommandBars(1).Controls.Add(msoControlPopup, , , , True)
    With cbMenu
        .Caption = "&My menu"
        .Tag = "MyTag"
        .BeginGroup = False
    End With
    ' or add to an existing menu (use the next line instead of the previous 6 lines)
    'Set cbMenu = Application.CommandBars.FindControl(, 30007) ' Tools-menu
    If cbMenu Is Nothing Then Exit Sub ' didn't find the menu...

    ' add menuitem to menu
    With cbMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "&Menu Item1"
        .OnAction = ThisWorkbook.Name & "!Macroname"
    End With

    ' add menuitem to menu
    With cbMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "&Menu Item2"
        .OnAction = ThisWorkbook.Name & "!Macroname"
    End With

    ' add a submenu
    Set cbSubMenu = cbMenu.Controls.Add(msoControlPopup, 1, , , True)
    With cbSubMenu
        .Caption = "&Submenu1"
        .Tag = "SubMenu1"
        .BeginGroup = True
    End With

    ' add menuitem to submenu (or buttons to a commandbar)
    With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "&Submenu Item1"
        .OnAction = ThisWorkbook.Name & "!Macroname"
        .Style = msoButtonIconAndCaption
        .FaceId = 71
        .State = msoButtonDown ' or msoButtonUp
    End With

    ' add menuitem to submenu (or buttons to a commandbar)
    With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "&Submenu Item2"
        .OnAction = ThisWorkbook.Name & "!Macroname"
        .Style = msoButtonIconAndCaption
        .FaceId = 72
        .Enabled = False ' or True
    End With

    ' add a submenu to the submenu
    Set cbSubMenu = cbSubMenu.Controls.Add(msoControlPopup, 1, , , True)
    With cbSubMenu
        .Caption = "&Submenu2"
        .Tag = "SubMenu2"
        .BeginGroup = True
    End With

    ' add menuitem to submenu submenu
    With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "&Submenu Item1"
        .OnAction = ThisWorkbook.Name & "!Macroname"
        .Style = msoButtonIconAndCaption
        .FaceId = 71
        .State = msoButtonDown ' or msoButtonUp
    End With

    ' add menuitem to submenu submenu
    With cbSubMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "&Submenu Item2"
        .OnAction = ThisWorkbook.Name & "!Macroname"
        .Style = msoButtonIconAndCaption
        .FaceId = 72
        .Enabled = False ' or True
    End With

    ' add menuitem to menu
    With cbMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "&Remove this menu"
        .OnAction = ThisWorkbook.Name & "!RemoveMenu"
        .Style = msoButtonIconAndCaption
        .FaceId = 463
        .BeginGroup = True
    End With
    Set cbSubMenu = Nothing
    Set cbMenu = Nothing
End Sub

Sub RemoveMenu()
' may be automatically executed from an Auto_Close macro or 
' a Workbook_BeforeClose eventmacro
    DeleteCustomCommandBarControl "MyTag" ' deletes the new menu
End Sub

Private Sub DeleteCustomCommandBarControl(CustomControlTag As String)
' deletes ALL occurences of commandbar controls with a tag = CustomControlTag
    On Error Resume Next
    Do
        Application.CommandBars.FindControl(, , CustomControlTag, False).Delete
    Loop Until _
        Application.CommandBars.FindControl(, , CustomControlTag, False) Is Nothing
    On Error GoTo 0
End Sub

Sub Macroname()
' used by the menuitems created by the CreateMenu macro
    MsgBox "This could be your macro running!", vbInformation, ThisWorkbook.Name
End Sub

On the page CommandBars you can find the tool CommandBar Face ID that can help you find the different symbols associated with the different Face ID numbers. You will also find the tool CommandBar Tools that will help you to find the ID numbers of the built-in CommandBar controls. The page also includes more examples on how to create menus and submenus.

Document last updated 2000-02-05 12:47:00 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-11-14 10:05:35 CET):

Re: Add FaceID to Popup Button?
As you already have found out, the FaceID property is not supported by the commandbarpopup object.
If you use the Object Browser (press F2 in the Visual Basic Editor), you can find all properties, methods and events supported by every object.

Ming Hua from Boston, Massachussetts, US wrote (2006-11-14 01:45:35 CET):

Add FaceID to Popup Button?
How about FaceID's? I can add them to the menu items, but cannot add them to the popup buttons (msoControlPopup). Is this also a limitation?
Thank you very much.

Ole P. from Norway wrote (2006-11-13 23:26:12 CET):

Re: How to add tooltip text to the menu items
Tooltips are only displayed for the root level controls on a commandbar/toolbar (e.g. a menu name), not for the menu items.

Ming Hua from Boston, Massachussetts, US wrote (2006-11-13 03:09:26 CET):

How to add tooltip text to the menu items
Could you please show me how to add tooltips to each of the menu items? I have tried use .tooltiptext = "click click", but does not work.

Thank you very much.

Ole P. from Norway wrote (2006-01-23 23:11:05 CET):

Re: How to code a line for seporate group of sub menu ?
This should be possible to determine by the example above...

Prayad from Thailand wrote (2006-01-23 13:04:19 CET):

How to code a line for seporate group of sub menu ?
How to code a line for seporate group of sub menu ?

Ole P. from Norway wrote (2005-01-10 10:08:59 CET):

Re: Custom Excel ShortCut Menu
This is possible, take a look at this example.

AleXcel from Oliveira/MG - Brazil wrote (2005-01-10 03:48:01 CET):

Custom Excel ShortCut Menu
As I can customise a Word ShortCut Menu (CommandBars("Text").Controls...) and then inserting macro calling arguments, I would like to do the same on Excel's ShortCut Menu.
Is it possible?

Ole P. from Norway wrote (2004-10-25 09:27:53 CET):

Re: How to bold the caption of a button
There is no Bold property available for the CommandBar captions.
You can change the font properties for your Desktop, but this will apply to all menu captions.

Michelle wrote (2004-10-25 08:44:48 CET):

How to bold the caption of a button
I have added a button to commandbar. How can i bold the caption of it?