The Command Bars Object Model

In Microsoft Excel 97, the CommandBars object model replaces both the Toolbars and MenuBars object models from Microsoft Excel 5/95. This allows you to combine text, buttons, or both on any of your menus or toolbars, giving you much greater flexibility than in previous versions of Microsoft Excel. Because Microsoft Excel still displays a command bar with menus and a set of command bars containing toolbar buttons, you generally want to follow the same interface design for your custom add-ins.

The following procedure demonstrates how to add a new menu item to the Tools menu of the Worksheet Menu Bar command bar:

Sub AddMenuItem()
    Dim oMenu As CommandBarPopup
    Dim oItem As CommandBarPopup
    Dim oSub1 As CommandBarButton
    Dim oSub2 As CommandBarButton   
    Set oMenu = CommandBars("Worksheet Menu Bar") _
        .Controls("Tools")
    Set oItem = oMenu.Controls.Add(Type:=msoControlPopup)
    oItem.Caption = "My Menu Item"
    Set oSub1 = oItem.Controls.Add(Type:=msoControlButton)
    oSub1.Caption = "Sub Menu 1"
    oSub1.OnAction = ThisWorkbook.Name & "!Proc1"
    Set oSub2 = oItem.Controls.Add(Type:=msoControlButton)
    oSub2.Caption = "Sub Menu 2"
    oSub2.OnAction = ThisWorkbook.Name & "!Proc2"
End Sub

The Menu Editor is no longer available in Microsoft Excel 97. All modifications to existing command bar menus must be made programmatically. You can also create your own custom command bars and attach them to a specific project. These command bars can include both menus and tools, and your code can hide or display the built-in command bars as needed.

Attaching custom command bars is handled via the Tools/Customize menu. You'll find the Attach button on the Toolbars tab. Beyond the first time the workbook is opened on a particular machine, the custom command bar will not hide or show itself automatically. The custom command bar will be available to all other workbooks unless your code explicitly controls its visibility.