Programming Excel with VBA and .NET

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