Programming Microsoft Office Command Bars
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
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.