Working with Command Bar Controls

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Each CommandBar object has a CommandBarControls collection, which contains all the controls on the command bar. You use the Controls property of a CommandBar object to refer to a control on a command bar. If the control is of the type msoControlPopup, it also will have a Controls collection representing each control on the pop-up menu. Pop-up menu controls represent menus and submenus and can be nested several layers deep, as shown in the second example below.

In this example, the code returns a reference to the New button on the Standard toolbar:

Dim ctlCBarControl As CommandBarControl

Set ctlCBarControl = Application.CommandBars("Standard").Controls("New")

Here the code returns a reference to the Macros… control on the Macro pop-up menu on the Tools menu on the "Menu Bar" main menu bar:

Dim ctlCBarControl As CommandBarControl

Set ctlCBarControl = Application.CommandBars("Menu Bar").Controls("Tools") _
   .Controls("Macro").Controls("Macros...")

Note   The "Menu Bar" CommandBar object refers to the main menu bar in Microsoft® Word, Microsoft® PowerPoint®, and Microsoft® Access. The main menu bar in Microsoft® Excel is called "Worksheet Menu Bar." To experiment with sample code that refers to the "Menu Bar" CommandBar object in Excel, simply change the reference from "Menu Bar" to "Worksheet Menu Bar."

Because each pop-up menu control is actually a CommandBar object itself, you also can refer to them directly as members of the CommandBars collection. For example, the following line of code returns a reference to the same control as the previous example:

Set ctlCBarControl = Application.CommandBars("Macro") _
   .Controls("Macros...")

When you have a reference to a control on a command bar, you can access all available properties and methods of that control.

Note   When you refer to a command bar control by using the control's Caption property, you must be sure to specify the caption exactly as it appears on the menu. For example, in the previous code sample, the reference to the control caption "Macros..." requires the ellipsis (…) so it matches how the caption appears on the menu.

See Also

Working with Command Bars | Manipulating Command Bars and Command Bar Controls with VBA Code | Getting Information About Command Bars and Controls | Creating a Command Bar | Hiding and Showing a Command Bar | Copying a Command Bar | Deleting a Command Bar | Preventing Users from Modifying Custom Command Bars | Working with Personalized Menus | Working with Images on Command Bar Buttons | Adding Controls to a Command Bar | Showing and Enabling Command Bar Controls | Visually Indicating the State of a Command Bar Control | Working with Command Bar Events