O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Create a Menu in Code

Access to menus in Excel is provided through the Office object model.

To create a new top-level menu in code:

  1. Get a reference to the menu bar on which you want to create the new top-level menu.

  2. Add a pop-up menu control to the menu bar and set its Caption and Tag properties.

  3. Add button menu controls to the pop-up menu and set the Caption, OnAction, ShortcutText, and other properties.

For example, the following code creates a top-level menu on the worksheet menu bar that is very similar to the menu shown in Figure 19-10:

Sub BuildMenu( )
    Dim cb As CommandBar, cpop As CommandBarPopup, cbtn As CommandBarButton
    ' Get the menu bar (CommandBar).
    Set cb = Application.CommandBars("Worksheet Menu Bar")
    ' Add top-level menu item (CommandBarPopup).
    Set cpop = cb.Controls.Add(msoControlPopup, , , , True)
    cpop.Caption = "&Run2"
    ' The Tag property makes it easy to delete this menu later.
    cpop.Tag = "Run2"
    ' Add items to the menu (CommandBarButton).
    Set cbtn = cpop.Controls.Add(msoControlButton, , , , True)
    ' Set menu item properties.
    cbtn.Caption = "Sample &1"
    cbtn.OnAction = "Sample1"
    ' Add a second item.
    Set cbtn = cpop.Controls.Add(msoControlButton, , , , True)
    cbtn.Caption = "Sample &2"
    cbtn.OnAction = "Sample2"
    ' Add a third item.
    Set cbtn = cpop.Controls.Add(msoControlButton, , , , True)
    cbtn.Caption = "Run &All"
    cbtn.ShortcutText = "Ctrl+Shift+A"
    cbtn.OnAction = "TestMenus"
    ' Add a separator bar before this item.
    cbtn.BeginGroup = True
End Sub

The last argument for ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required