An easy-to-use interface is always an appreciated commodity in an application. It’ll help your users learn how to use the application more quickly. It can also keep users out of trouble by limiting the number of options they can execute. To this end, you’ll probably suggest that built-in toolbars be totally inhibited or at least modified to present only those items the user really needs to access. You may even build custom toolbars with the same goal in mind.

The Office Command Bar object model has a lot to offer but is also a bit awkward to work with if you don’t know exactly what you’re doing. Each Office application contains dozens of built-in command bar objects, which in turn can contain dozens more command bars. In Access, these built-in objects are the menu bar, a variety of toolbars, and pop-up menus, and they’re stored with the database in which they’re created—unlike the other applications that store the command bars separately.

Working through the object model and identifying which object is at every level is the key to correctly referencing objects so you can manipulate them via Visual Basic for Applications (VBA) code. In this article, I’ll consistently apply the same syntax to the different levels of the model so you can see both visually and programmatically how to interact with the model levels.

Referencing the right library
To interact with the command bar object model, you must reference the appropriate library. To do so, launch the Visual Basic Editor (VBE) and select the References option from the Tools menu. Then check the Microsoft Office 10.0 Object Library item. Click OK to close the References dialog box.

The CommandBar object: Level one
Each menu bar, toolbar, and pop-up menu is a CommandBar object that consists of a collection of control objects. The menu bar—named Menu Bar—is actually the current menu bar in any particular view, and each view has just one. Each menu command on the menu bar performs a task or presents a submenu. In contrast, a view can present many toolbars, which then present buttons and tools, that perform a task when clicked. The menu bar and toolbars don’t look the same, but underneath what you see, they’re both the same type of object—the CommandBar object.

Cycling through the CommandBars collection, which represents all the CommandBar objects for a particular application, is a simple task. Enter the procedure in Listing A in a standard module and then run the procedure by clicking the Run Macro button on the VBE’s Standard toolbar.

First, this simple function returns the number of CommandBar objects in the collection, which in our example is 173, as you can see in Figure A. (The list is actually very long; Figure A displays only the first few items.) It then lists each object by name, specifies whether it’s enabled or disabled (True and False, respectively), and identifies the object by type.

Figure A
List all the CommandBar objects in an application.

Refer to Table A for the types of CommandBar objects. If you scroll through the list in the Immediate window, you’ll find only one menu bar (type 1). Most of the pop-up types are listed toward the bottom of the list.
Table A


Type
Constant
Integer value
Toolbar  msoBarTypeNormal 0
Menu bar msoBarTypeMenuBar 1
Popup (or shortcut) msoBarTypePopup 2

CommandBar types

The controls: Level two
Each CommandBar object contains any number of CommandBarControl objects. More specifically, these controls can be one of three specialized objects:

  • CommandBarButton: A button control that displays text, an icon, or both on a command bar
  • CommandBarComboBox: A custom combo box on a command bar
  • CommandBarPopup: A control that displays another menu when you click on it

There are many control types at this level; Table B lists the more common ones. These aren’t types of objects; they’re the type of controls you’ll encounter via each of the three above objects.
Table B


Type
Constant
Integer value
Custom msoControlCustom 0
Button msoControlButton 1
Edit msoControlEdit 2
Dropdown msoControlDropdown 3
Combo Box msoControlComboBox 4
Button Dropdown msoControlButtonDropdown 5
Generic Dropdown msoControlGenericDropdown 8
Popup msoControlPopup 10
Button Popup msoControlButtonPopup 12
Split Button Popup msoControlSplitButtonPopup 13
Split Button Popup (MRU) msoControlSplitbuttonMRUPopup 14

CommandBarControltypes

Because a command bar can contain different types of control objects, you’ll probably find working with the generic CommandBarControl object less complicated and more dependable than the others—at least in the beginning. Unfortunately, the CommandBarControl object is more limited in properties than the more specific control objects.

For example, the CommandBarControl object doesn’t have a Name property. To identify a control, use the Caption property, the Index value, or the ID property (which is assigned internally). In a custom control, you could use the Tag property to store a name if you need to present names to the user.

The procedure in Listing B lists information about the controls on any menu bar, toolbar, or pop-up menu.

Enter the following statement in the Immediate window:
 
?GetControlNames(“Menu Bar”)
 

The results are ugly because each item contains the & character (which indicates a hot key), as you can see in Figure B. Remember, these are the Caption settings, not the control names. For the most part, you won’t be cycling through these collections to create a list of controls to present to your users. You’ll be gleaning information during the development stage, and the Caption property is probably good enough for that purpose. Notice that I’ve added a property—ID—to the printed list.

Figure B
List information about each control on a specific menu bar, toolbar, or pop-up control.

The submenus: Level three
There’s one more level, which is unique to the menu bar—submenus. The key to listing the contents of this collection is knowing that it even exists. (The collection is actually a collection of another control’s collection.) Listing C cycles through the first layer of submenus.

Figure C shows the results of cycling through the Menu Bar’s File menu using this statement:
 
?GetSubMenuNames(“Menu Bar”, “File”)

Figure C
List of submenu items

Referencing all three levels
The above examples are a convenient way to mesh out the object model and its hierarchy, but you won’t often work with these objects in this way. Rather, you’ll reference them and reset properties in order to manipulate menu bars, toolbars, and their controls. Previously, we determined that there are three levels of controls:

  • The menu bar or toolbars
  • The menu command and toolbar buttons on the menu bar or toolbar
  • Submenus (and lower) that offer subsequent menu choices from a menu command; this third level can be repeated to create any number of levels from one control

The first level, the CommandBar object level, is the easiest to reference using the following syntax:
 
Dim cbarMenu As CommandBar
Set cbarMenu = CommandBars(“barname“)
cbarMenu.property= value
 

The procedure in Listing D uses this form to toggle a menu bar or toolbar between enabled and disabled. To demonstrate this procedure, enter the following statement in the Immediate window:
 
?ToggleCommandBar(“Menu Bar”)
 

Return to Access and you’ll see that the menu bar is gone. Return to the Immediate window and execute ToggleCommandBar() again to restore the menu bar.

At the second level, you’ll find all the menu commands and buttons on the menu bar or toolbar. While the type of controls may differ, you can reference them all using the CommandBarControl object. In this case, you simply refer to the CommandBar object’s collection using this syntax, where controlname is a string that identifies the name of the control:
 
commandbarobject.Controls(controlname)
 

Use the procedure in Listing E to modify controls on a menu bar or a toolbar. It’s very similar to the previous procedure in that it declares a CommandBar object. However, it goes one level deeper and references a control in the CommandBar object’s Controls collection.

Simply pass the control and command bar objects by name. For example, in the Immediate window, enter this statement:
 
?ToggleCommandBarControl(“File”, “Menu Bar”)
 

This disables the File menu on the Menu bar, as shown in Figure D. Return to the Immediate window and executethe procedure again to enable the File menu.

Figure D
Disable a menu item.

The third level consists of submenus that perform a task or lead to yet another submenu level. For example, you might want to disable or enable the New submenu item on the File menu on the menu bar. For obvious reasons, you’ll use this syntax only with the menu bar—toolbars don’t contain submenus (although you could probably add one to a custom toolbar). This time, you’re cycling through a control object’s collection of controls in this form:
commandbarobject.Controls(ctl1).Controls(ctl2)

Listing F references a submenu using this syntax.

In the Immediate window, pass both controls and the menu object to the procedure using this statement:
?ToggleCommandBarSubmenu(“File”, “New…”, “Menu Bar”)

Figure E shows the disabled New submenu on the File menu.

Figure E
Disable submenu items.

Planning for errors
A number of situations will produce runtime errors:

  • You may have noticed that the string passed in the last example includes the ellipses character (…). You must include these when appropriate or the procedure will return an error.
  • If you attempt to modify a disabled command bar or control object, the procedure may return an error. An object can be inhibited (not presently available or visible) without being programmatically disabled. When this is the case, you can still modify the object.
  • Conflicting arguments will return errors; you must pass the correct strings the corresponding objects. For instance, passing ToggleCommandBarSubmenu() the following strings would return an error because there is no Delete control in the File control’s collection: File, Delete, Menu Bar.

Level the playing field
Don’t let working with the command bar and controls frustrate you. Being familiar with the hierarchy is the key to working with these objects successfully. Knowing the proper syntax for referencing all the objects once you know they exist will go a long way toward working through the many levels of objects with ease.