Programming Microsoft Office Command Bars

Article
09/12/2006

Paul Cornell
Microsoft Corporation

April 4, 2002

Command bars are used throughout Microsoft® Office to allow users to carry out actions in Office applications. Command bars come in two forms—toolbars and menus. If you've ever performed actions such as creating a new Office document by clicking New on the File menu, searching for Help using the Type a question for help box, or clicking the Save button on the Standard toolbar, you're familiar with command bars. In this month's column, I introduce you to creating and modifying both built-in and custom Office command bars using Visual Basic® for Applications (VBA).

Command Bar Storage Locations

Command bar code is stored in various locations depending on which Office application you are using. The following table describes these locations.

Application	Command bar locations
Microsoft Access	Only the database in which the command bar is created.
Microsoft Excel	Can be created in an individual workbook or in the overall Excel workspace. If command bars are created in the Excel workspace, they are stored in the Excel.xlb file on the local computer.
Microsoft FrontPage®	Can be created only in the overall FrontPage workspace (not for specific Webs). Command bars are stored in the CmdUI.prf file on the local computer.
Microsoft Outlook®	Can be created only in the overall Outlook workspace. Command bars are stored in the Outcmd.dat file on the local computer.
Microsoft PowerPoint®	Can be created only in the overall PowerPoint workspace (not for particular presentations). Command bars are stored in the PPT.pcb file on the local computer.
Microsoft Word	Can be created in the Normal.dot template (available to all documents), in a user-defined template (available to any document based on that template), or in the active document (available only to that document).

While you can use VBA code to copy command bars within an Office application, you can't create command bars in one Office application and copy them into other Office applications.

Types of Command Bars

As stated earlier, command bars come in two forms—toolbars and menus. In addition to toolbars, there are two types of menus—menu bars and pop-up menus.

Toolbars contain menus, buttons, and other types of controls that can be used to carry out commands. Common toolbars include the Standard toolbar, the Formatting toolbar, the Web toolbar, and so on. To see the list of available toolbars for a particular Office application, click on the View menu in the Office application, point to Toolbars.
Menu bars contain pop-up menus. The main Menu Bar includes pop-up menus such as File, Edit, View, and so on.
Pop-up menus include menus that drop down from menu bars, submenus that cascade off of menu commands, and shortcut menus (also known as right-click menus). Pop-up menus contain commands; for example, the File pop-up menu contains commands such as New, Open, Save, Exit, and so on.

Types of Command Bar Controls

Although there are many types of command bar controls (these are the actual user-interface elements that are used to carry out users' actions in Office applications), only the following command bar controls can be created or modified through the Microsoft Office Object Library (MSO.DLL):

Command button: This control allows users to click a button to perform an action. Examples of command buttons are the Bold, Italic, and Underline command buttons on the Standard toolbar in Word.
Combo box: This control allows users to select a choice from a list or enter one of their own choices. An example of a combo box is the Font Size combo box on the Formatting toolbar in Word.
Drop-down list box: This control allows users to select a choice from a list, but does not allow users to enter one of their own choices. An example of a drop-down list box is the Display for Review drop-down list box on the Reviewing toolbar in Word 2002.
Text box: This control allows users to type information.
Pop-up menu: This control displays a menu containing child command buttons, combo boxes, drop-down list boxes, text boxes, or pop-up menus. Examples of pop-up menus include any of the pop-up menus on the main Menu Bar.

Creating or modifying any other type of command bar control will result in a run-time error.

Understanding the Command Bar Object Model

The CommandBars collection represents all of the command bars in a particular Office application. The CommandBars collection contains CommandBar objects representing individual command bars. Furthermore, the CommandBar object contains a CommandBarControls collection, which in turn contains CommandBarControl objects representing individual generic command bar controls. These controls can also be strongly typed as CommandBarButton objects, CommandBarComboBox objects, or CommandBarPopup objects representing command buttons, combo boxes, or pop-up menus, respectively. When you write code to work with custom command bar controls, you use these CommandBarButton, CommandBarComboBox, and CommandBarPopup objects. When you write code to work with controls that cannot be represented by one of these three objects, you use the generic CommandBarControl object.

Figure 1 provides a graphical representation of the command bar and command bar control objects in the Microsoft Office Object Library.

Figure 1. Command bar and command bar controls objects in the Microsoft Office Object Library.

Accessing Command Bars and Command Bar Controls

To access a command bar programmatically, use the CommandBars collection's Item property, specifying the desired CommandBar object's Name property. Likewise, to access command bar controls, use the CommandBarControls collection's Item property, specifying the desired CommandBarControl object's Caption property. Note that you can also specify an index number for the Item method.

The following code lists the names of all of the command bars in a given Office application.

Public Sub ListCommandBars()

    ' Purpose: Lists all available command bars in
    ' this Office application.

    Dim objCommandBar As Office.CommandBar
    Dim strResults As String
       
    Debug.Print "Command bars in this application:"
    
    For Each objCommandBar In Application.CommandBars
        
        Debug.Print objCommandBar.Name & _
            " (" & GetCommandBarType(objCommandBar.Type) & ")"
           
    Next objCommandBar
        
End Sub

The following code lists the captions of all of the controls on a given Office command bar.

Public Sub ListCommandBarControls _
        (ByVal objCommandBar As Office.CommandBar)
        
    ' Purpose: Given a command bar, lists all of the controls
    ' on the command bar.
   
    Dim objCommandBarControl As Office.CommandBarControl
    Dim strControlList As String
    
    strControlList = "Controls for the '" & objCommandBar.Name & _
        "' command bar:" & vbCrLf
    
    For Each objCommandBarControl In objCommandBar.Controls
    
        strControlList = strControlList & _
            objCommandBarControl.Caption & " (" & _
            GetCommandBarControlType(objCommandBarControl.Type) & _
            ")" & vbCrLf
        
    Next objCommandBarControl
    
    MsgBox strControlList
    
End Sub

You can call the ListCommandBarControls subroutine with code similar to the following:

Public Sub TestListCommandBarControls()

    Call ListCommandBarControls(Application.CommandBars.Item("Standard"))
    
End Sub

Creating, Deleting, Showing, and Hiding Command Bars and Command Bar Controls

To create a command bar, use the CommandBars collection's Add method. The Add method takes the following arguments:

The optional Name argument is a String value that specifies the name of the new command bar. If the Name argument is omitted, a default name is assigned to the command bar (such as Custom1).
The optional Position argument is an MsoBarPosition enumerated constant representing the position or type of the new command bar. The msoBarLeft, msoBarTop, msoBarRight, and msoBarBottom constants specify the left, top, right, and bottom coordinates of the new command bar. The msoBarFloating constant specifies that the new command bar won't be docked. The msoBarPopup constant specifies that the new command bar is a pop-up menu.
The MenuBar argument is a Boolean value; True replaces the active menu bar with the new command bar. The default value is False.
The Temporary argument is also a Boolean value; True specifies that the new command bar is temporary, which means the command bar will be deleted when the container application is closed. The default value is False.

To demonstrate how to create different types of command bars, the following code creates a toolbar.

Public Sub CreateToolbar()

    ' Purpose: Creates a sample toolbar.
    
    Dim objCommandBar As Office.CommandBar
    
    For Each objCommandBar In Application.CommandBars
    
        If objCommandBar.Name = "My New Toolbar" Then
        
            objCommandBar.Delete
        
        End If
        
    Next objCommandBar
            
    Set objCommandBar = Application.CommandBars.Add("My New Toolbar")
    
    ' To delete this toolbar, call:
    ' Application.CommandBars("My New Toolbar").Delete
    
End Sub

The following code creates a menu bar.

Public Sub CreateMenuBar()

    ' Purpose: Creates a sample menu bar.

    Dim objCommandBar As Office.CommandBar
    
    For Each objCommandBar In Application.CommandBars
    
        If objCommandBar.Name = "My New Menu Bar" Then
        
            objCommandBar.Delete
        
        End If
        
    Next objCommandBar
    
    Set objCommandBar = Application.CommandBars.Add _
        ("My New Menu Bar", , True)
        
    objCommandBar.Visible = True
    
    ' To delete this menu bar, call:
    ' Application.CommandBars("My New Menu Bar").Delete
    
End Sub

The following code creates a pop-up menu.

Public Sub CreatePopUpMenu()

    ' Purpose: Creates and displays a sample pop-up menu.

    Dim objCommandBar As Office.CommandBar
    
    For Each objCommandBar In Application.CommandBars
    
        If objCommandBar.Name = "My New Popup Menu" Then
        
            objCommandBar.Delete
        
        End If
        
    Next objCommandBar
    
    Set objCommandBar = Application.CommandBars.Add _
        ("My New Popup Menu", msoBarPopup)
    
    objCommandBar.ShowPopup _
        Application.Width / 2, Application.Height / 2
        
    ' To delete this pop-up menu, call:
    ' Application.CommandBars("My New Popup Menu").Delete
    
End Sub

Similarly, to add a command bar control to a command bar, use the CommandBarControls collection's Add method. Use the following MsoControlType enumerated constants when calling the CommandBarControls collection's Add method to add a command bar control to a command bar:

Use the msoControlButton constant for a command button.
Use the msoControlComboBox constant for a combo box.
Use the msoControlDropdown constant for a drop-down list box.
Use the msoControlEdit constant for a text box.
Use the msoControlPopup constant for a pop-up menu.
The following code creates a toolbar with various controls.

Public Sub CreateCommandBarWithControls()

    ' Purpose: Creates a sample command bar with a number
    ' of controls.

    Dim objCommandBar As Office.CommandBar
    Dim objCommandBarControl As Office.CommandBarControl
    Dim objCommandBarButton As Office.CommandBarButton
    Dim objCommandBarComboBox As Office.CommandBarComboBox
    Dim objCommandBarPopup As Office.CommandBarPopup
    
    For Each objCommandBar In Application.CommandBars
    
        If objCommandBar.Name = "Controls Demo" Then
        
            objCommandBar.Delete
        
        End If
        
    Next objCommandBar

    Set objCommandBar = Application.CommandBars.Add _
        ("Controls Demo")
        
    With objCommandBar.Controls
        
        Set objCommandBarButton = .Add(msoControlButton)
        
        With objCommandBarButton
            
            .Caption = "Get &Happy"
            .FaceId = 59
            .Style = msoButtonIconAndCaption
            .TooltipText = _
                "If you are happy and you know it, click here."
        
        End With
        
        Set objCommandBarComboBox = .Add(msoControlComboBox)
        
        With objCommandBarComboBox
            
            .AddItem "Blue"
            .AddItem "Green"
            .AddItem "Red"
            .AddItem "Yellow"
            .AddItem "Other"
            .Text = "Color"
            .Caption = "Color"
            .Style = msoComboNormal
            .TooltipText = "Select or type your favorite color."
        
        End With
        
        Set objCommandBarComboBox = .Add(msoControlDropdown)
        
        With objCommandBarComboBox
        
            .AddItem "Chocolate"
            .AddItem "Strawberry"
            .AddItem "Vanilla"
            .AddItem "Other"
            .AddItem "None"
            .Style = msoComboLabel
            .Caption = "Ice &Cream"
            .TooltipText = "Select your favorite ice cream flavor."
        
        End With
        
        Set objCommandBarComboBox = .Add(msoControlEdit)
        
        With objCommandBarComboBox
        
            .Caption = "Search"
            .Text = "Type search term"
            .TooltipText = "Type the term you want to find."
            
        End With
        
        Set objCommandBarPopup = .Add(msoControlPopup)
        
        With objCommandBarPopup
        
            .Caption = "&More"
            
            Set objCommandBarComboBox = .Controls.Add(msoControlDropdown)
        
            With objCommandBarComboBox
            
                .AddItem "5 (Excellent)"
                .AddItem "4"
                .AddItem "3"
                .AddItem "2"
                .AddItem "1 (Poor)"
                .Caption = "Rating"
                .Style = msoComboNormal
            
            End With
            
            Set objCommandBarButton = .Controls.Add(msoControlButton)
            
            With objCommandBarButton
            
                .Caption = "&Checked"
                .Style = msoButtonCaption
                
            End With
            
            Set objCommandBarButton = .Controls.Add(msoControlButton)
        
            With objCommandBarButton
            
                .Caption = "Interesting &Shape"
                .FaceId = 43
                .Style = msoButtonIconAndCaption
                
            End With
            
        End With
        
    End With
    
    objCommandBar.Visible = True
    
End Sub

To delete a command bar, use the CommandBar object's Delete method. To delete a command bar control, use the Delete method of the corresponding CommandBarButton object, CommandBarComboBox object, CommandBarControl object, or CommandBarPopup object.

Use the Visible property to show or hide a command bar or command bar control (True to show and False to hide).

To copy a command bar, you use the CommandBars collection's Add method to create a new command bar of the same type as the one you're copying. You then use the CommandBarControl object's Copy method to copy each control from the original command bar to the new command bar.

Working with Images on Command Bar Buttons

You can add built-in Office application images to command bar buttons. Each built-in image has an associated face ID that can be referenced by using the CommandBarButton object's FaceID property. Note that these images are referenced by number only and do not have readable names.

Because there are literally thousands of available images that differ with each Office application, the following code creates a toolbar and fills it with command buttons containing built-in images corresponding to a range of face IDs that you provide. Each image has a ToolTip listing the image's face ID. For best results, you should not provide ranges of more than a couple hundred face IDs.

Public Sub ListButtonPicturesAndIDs(ByVal intStart As Integer, _
        ByVal intEnd As Integer)
        
    ' Purpose: Given a starting and ending number, creates a
    ' command bar with pictures corresponding to the face IDs
    ' in the range of numbers provided.

    Dim objCommandBar As Office.CommandBar
    Dim objCommandBarButton As Office.CommandBarButton
    Dim intButton As Integer
    
    On Error GoTo ListButtonPicturesAndIDs_Err
    
    If intStart > intEnd Then
        
        MsgBox "Ending number must be smaller than starting number. " & _
            "Please try again."
        
        Exit Sub
    
    End If
    
    For Each objCommandBar In Application.CommandBars
        
        If objCommandBar.Name = "Button Pictures and IDs" Then
            
            objCommandBar.Delete
        
        End If
    
    Next objCommandBar
    
    Set objCommandBar = _
        Application.CommandBars.Add("Button Pictures and IDs", , , True)
    
    For intButton = intStart To intEnd
    
        Set objCommandBarButton = _
            objCommandBar.Controls.Add(msoControlButton, , , , True)
    
        With objCommandBarButton
            .FaceId = intButton
            .TooltipText = "FaceID = " & intButton
        End With
    
    Next intButton
    
    objCommandBar.Visible = True
    
ListButtonPicturesAndIDs_End:

    Exit Sub
    
ListButtonPicturesAndIDs_Err:
    
    Select Case Err.Number
        
        Case -2147467259    ' Invalid FaceIDs.
            MsgBox "Invalid range of numbers for face IDs. " & _
                "Please try again."
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description
    
    End Select
    
    Resume ListButtonPicturesAndIDs_End
                
End Sub

You can call the ListButtonPicturesAndIDs subroutine with code similar to the following:

Public Sub TestListButtonPicturesAndIDs()

    Call ListButtonPicturesAndIDs(100, 200)
    
End Sub

Working with Command Bars and Command Bar Controls in the Visual Basic Editor

The code that I have shared up to this point does not work inside of the Visual Basic Editor. To work with command bars and command bar controls in the Visual Basic Editor, you must first set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library (VBE6EXT.OLB). You can then call the CommandBars collection through the VBE object's CommandBars property. The VBE object is available through the Application object's VBE property. So all you really need to do is to insert the code VBE between the code Application and CommandBars as follows:

...
For Each objCommandBar In Application.VBE.CommandBars
...
Set objCommandBar = Application.VBE.CommandBars.Add _
        ("Controls Demo")
...

And so on.

For More Information

CommandBars Collection Object (reference)
CommandBarControls Collection Object (reference)
Command and Menu Bars (technical article)
Finding Face ID Numbers for Your Microsoft Office 97 Toolbars (technical article)

Paul Cornell works for the MSDN Online Office Developer Center and the Office developer documentation team. Paul also writes the Office Power User Corner column for the Office Assistance Center. He spends his free time with his wife and two daughters.