Timesaving utility forms and add-ins are a large part of the developer’s toolbox. One example is building a simple utility form in Access that displays command bar and control objects, so you can enable or disable them with just a click of a button. The utility form is simple in design and code. You build the form, add the code, reference the Microsoft Office 10.0 Object Library, and go. It couldn’t be simpler.

But you’ll probably want to enhance it to perform tasks that are specific to a particular database or your development process. The example I’ll show you in this article provides the shell; just add command buttons and the appropriate code.

If you’re not familiar with the command bar and control objects and collections, consider reading “Referencing command bar and control objects in Access” first. That article explores the three levels—command bar object, command bar control object, and submenu controls—and shows you how to reference each level.

Creating the form
The utility form shown in Figure A comprises three sets of controls: an option group, three list box controls, and three command buttons. Refer to Table A for the appropriate form and control properties. When you create this form, name it frmBarUtility.

Figure A
Use this simple form to quickly manipulate command bar and control objects.

Table A


Object
Property
Setting
Form 
 

 

 

 
 		Caption Manipulate Menu bars and Toolbars
Scroll Bars Neither
Record Selectors No
Navigation Buttons  No
Dividing Lines No
Option Group
 
 		Name Manipulate Menu bars and Toolbars
Default Value  1
Option Buttons
 

 

 

 

 
 		Name optAll
Option Value 1
Name  optBuiltin
Option Value 2
Name optCustom
Option Value 3
List Controls
 

 

 

 

 
 		Name lstBars
RowSourceType Value List
Name lstLevel1
RowSourceType Value List
Name listLevel2
RowSourceType Value List
Command Buttons
 

 

 

 

 
 		Name cmdToggle1
Caption Toggle Command Bars
Name cmdToggle2
Caption Toggle Controls
Name cmdToggle3
Caption Toggle Submenus

Form properties

The option group lists three options that filter the command bar objects for the current database. Specifically, the All option displays all of the command bar objects, menu bars, toolbars, and shortcut menus. The Built-in option displays all the Access interface command bar objects. The last option, Custom, displays the command bar objects you’ve created.

Selecting an option from the option group (All, Built-in, or Custom), displays a list according to the selected option, in the first list box control. The two remaining list controls will display control objects (menu commands and tools) at the first and second level. The first level is the menu command or tool that you see on the menu bar or toolbar, respectively. The second level is for submenus (when applicable).

The toggle buttons beneath the two control object lists controls enable or disable the selected item, according to the selected object’s current state. If it’s disabled, clicking that control’s corresponding toggle button will enable it, and vice versa.

Adding the code
After creating the form, launch the Visual Basic Editor (VBE) to enter the code that runs the form. If the form is still in Design view, click the Code button on the Form Design toolbar. Or, press [Alt][F11]. Once in the VBE, reference the Microsoft Office 10.0 Object Library by choosing References from the Tools menu and then checking that library. Then, click OK to return to the Close The References dialog box. Next, add the seven event procedures in Listings A through G to the form’s module. Be sure to save the form after you add the code.

Using the form
Initially, the form’s Load event (Listing D) displays a list of all the enabled command bar objects in the current database (see Figure A). In Form view, click one of the three options to display a list of command bar objects in the first list. Doing so triggers the option group’s After Update event (Listing E). The For Each statement cycles through all the objects in the CommandBars collection. When the appropriate conditions are met, the procedure adds the current object’s name to a list stored in the variable strList, and separates each with a semicolon character (;). The first condition is that the object be enabled. Then, the object is added to the list as follows:

  • ·        If the All option is selected, all objects are added to the list.
  • ·        If the Built-in option is selected, only built-in objects are added to the list. By built-in, I mean the command bars that are part of the Access interface.
  • ·        If the Custom option is selected, only custom objects are added to the list. By custom, I mean the command bars that you create.

After the list is complete, the statement:
 
Me!lstBars.RowSource = strList

sets the first list control’s Row Source property to the variable that contains the list. Access immediately updates the control by displaying the list (see Figure A).

Once you’ve experimented a bit with the three options, click All to reset the list contents to all of the enabled command bar objects. Then, scroll to the bottom of that list and select Menu Bar. Doing so executes lstBars’ After Update event (Listing F). Using a For Each loop, the event procedure displays all of the enabled control objects (menu commands and tools) on the selected command bar object, which happens to be the Menu Bar in this case, as shown in Figure B.

Figure B
Display all the controls for the selected command bar object.

Notice that the list (lstLevel1) displays captions because tools don’t have a Name property. Since the procedure must deal with both menu commands and tools, it’s easier to use the CommandBarControl object than to write recursive code that handles both. The unfortunate side effect is that this object doesn’t have a Name property. This limitation shouldn’t be a problem in a utility form. However, you probably won’t want to display such a list to users, because they might find the ampersand (&) characters (which denote hot keys) confusing. (It’s probably not a good idea to provide users with a menu manipulating utility form anyway.)

This procedure uses the same For Loop strategy discussed previously, but there’s one difference. First, the On Error Resume Next statement right before the For Each loop captures errors that occur when the current object in the Controls collection isn’t a CommandBarControl object. Some tools will raise this error.

The second difference is that the resulting list will display submenus in the third list control (lstLevel2). Most tools don’t display submenus, so the statement:
 
If cbarControl.Type = msoBarTypeMenuBar

accommodates only the appropriate control type—submenus. If the control type isn’t a submenu, it doesn’t appear in the third control’s list. There’s nothing to display. For instance, select Form Design and &Save from the first and second lists, respectively, and the last control displays nothing, as shown in Figure C. The &Save button (a tool) has no submenu items to display.

Figure C
Not every control offers submenu items.

When a control does have submenu items, selecting the control in the second list will display those submenus in the third list. Figure D shows the results of selecting Form Design and View from the first and second control lists, respectively. Even though the View button takes the form of a control, it lists submenus, and the event procedure passes those through to the list.

Figure D
The third list control displays submenu items.

Using the toggle buttons
Beneath each list control is a toggle button, which you can click to enable or disable the selected item in the list above. The event procedures (Listings A, B, and C) are all very similar. They cycle through different collections—command bars, menu command controls, and submenu controls. To illustrate, select Form View in the first list and click the Toggle Command Bars button (cmdToggle1) to inhibit the Form View toolbar. Click the button again to redisplay it. Similarly, click a control in the second or third list to enable or disable the selected item in that list.

The toggle buttons don’t need a For Each loop. Instead, these event procedures grab the name of the command bar, the control, and the submenu, accordingly, and then set the Enabled property to the opposite of what it is at the time.

Possible enhancements
The example utility form is as simple as possible to avoid confusion. There are a number of enhancements you could add. For instance, before displaying items in the first list box, you could filter out specific types of command bar objects using the three constants listed in Table B.
Table B


Type
Constant
Integer value
Toolbar msoBarTypeNormal 0
Menu bar msoBarTypeMenuBar 1
Shortcut msoBarTypePopup 2

Command bar types

The sample form displays all command bar objects, but you could eliminate any of the above from the list. For instance, to omit shortcut menus, you’d add an If statement immediately following the For Each statement in the option group’s After Update event (fraMenuOptions_AfterUpdate()):
 
If menu.Type <> msoBarTypePopup Then
…
End If

In other words, if the current command bar object isn’t a shortcut menu, add it to the list—eliminating shortcut menus from the list.

You could add the appropriate code if you know you’ll always be working with a specific type of command bar. Or you could make the process more dynamic by adding a second set of options, probably check boxes that list the different types of command bars, and then filter the resulting list accordingly. For instance, you could view all (but only) menu bars, toolbars, or shortcut menus. Or, you might want to view only built-in toolbars or only custom shortcut menus, and so on.

Make it your own
Manipulating menus and toolbars isn’t restricted to enabling and disabling, which is all this one does. You might want to change a control’s Caption property, add a Tag setting, or change the Style property, and so on. Simply add a command button and the appropriate code, now that you know how to reference the necessary objects. Be sure to add error-handling where required. Furthermore, if you like the form and use it extensively, consider making it an add-in, so you don’t have to remember to reference the Office object library every time you import it into a database.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database and Web technologies. Her most recent books are: SQL: Access to SQL Server, by Apress, Mastering Dreamweaver MX Databases, by Sybex and Absolute Beginner’s Guide to Microsoft Access 2002, by Que.