10 tips for customizing menus and toolbars in your Access apps

by Susan Harkins | Jan 03, 2007 8:00:00 AM

Tags: Microsoft Office, Susan Harkins, toolbar, Microsoft Access, menu bar, menu command, built-in menu bar

Takeaway: When you build an Access application, you'll generally want to tailor the menus and toolbars so that users have all the tools they need but won't stumble over the built-in options. These tips will help you manipulate your interface so that it's effective and user friendly.

This article is also available as a PDF download.

A custom Access application shouldn't lay itself open to misuse by giving users more options than needed. That means you'll probably inhibit the built-in menu bar and toolbars and replace them with custom objects that provide only the options your users need. Fortunately, custom tools are easy to build and manipulate. Here are a few tips to facilitate your work with them.

---

Note

For most of the functions in these tips to work, you must first reference the Microsoft Office Object Library. In the Visual Basic Editor (VBE), select References from the Tools menu. Then, check the library item and click OK.

---

#1: Give forms and reports a default menu

When you open a form or report, Access displays the built-in menu bar, which features the familiar menu commands (File, Edit, etc.). If you inhibit this menu bar for a custom application, or if you want to present options and tasks that are specific to that particular form or report, you can build a custom menu bar that Access will display instead of the built-in menu bar when the user opens the form or report.

To do this, simply set the form's Menu Bar property to the appropriate custom bar object using the property field's drop-down list. When you leave this property blank, Access displays the built-in menu bar or the application's global menu bar (see #4).

To set this property using VBA, execute a statement in the following form:

Forms!frmname.MenuBar = menubarname

When using a form for different purposes, such as browsing records and data entry, you can use this property to display different custom menus for each view. Just set the property to the appropriate menu bar, dependent on some flag you can set or pass, when you open the form.

To reset the menu bar to the built-in menu bar, use a statement in the following form:

Forms!frmname.MenuBar = " "

#2: Add a custom command

It's easy to duplicate built-in menu commands and add them to a custom menu bar. Choose Customize from the Tools menu to launch Customize mode, which is similar to a form, report, or query's Design View. Next, check the appropriate menu bar on the Toolbars tab. With the menu bar in Customize mode, click the Commands tab and drag the appropriate menu command to the menu bar. It's that simple.

You won't always want to duplicate existing menu commands. Occasionally, you'll need to offer a custom menu option. For instance, you might want to offer a menu option that allows the user to print a specific report. When this is the case, choose the Custom menu command from the Commands tab. Once the Custom menu command is a part of the menu bar object, right-click on it, choose Properties, and use the On Action option to identify the procedure that will process that menu command's task.

#3: Names matter

Each new custom menu bar you build will need a unique name. Don't try to name any of your custom menu bar objects Menu Bar because that's the name of the build-in menu bar. If you try to use this name, either using the Customize dialog box or VBA code, Access will return an error.

Consider tagging each custom bar object with a prefix that identifies the object, such as cbo or just cb, for command bar object or command bar. The prefix is self-documenting. (Don't use cbo if you also use that to prefix combo box objects. Nothing bad will happen if you do, but the use of the same prefix for two different objects might confuse you or others later.)

#4: Create a global menu bar

You can create a global menu bar and use the Application object to enable it instead of using the built-in menu bar. To do so manually, choose Startup from the Tools menu. Then, choose the menu bar from the Menu Bar option's drop-down list. To automate the process using VBA, execute the following statement:

Application.MenuBar = menubarname

Just remember that the change isn't permanent, so you should execute the Application statement in the application's startup routine. In addition, a form or report's default menu bar (#1), takes precedence over the global menu bar.

#5: Quickly move between menu bars

If you replace the built-in menu bar with a custom one, Access hides the built-in menu bar. But it doesn't permanently delete the built-in menu bar, and you still have full access to it. Pressing [Ctrl][F11] will toggle between displaying and hiding the built-in menu bar.

#6: Cycle through the CommandBars collection

It's a simple matter to list all the CommandBar objects--the menu bars and toolbars--in an application. Drop the following function procedure into a module, run it, and view the resulting list in the Immediate window:

Function ListCommandBarObjects()
  'List all the CommandBar objects in the
  'application in the Immediate window.
  Dim cb As CommandBar
  For Each cb In CommandBars
    Debug.Print cb.Name
  Next
End Function

Although you might use this procedure during the development phase, it's a good way to document the menu bars, toolbars, and popup menus in an application. Simply cut and paste the list in the Immediate window to a text file and save or print, or both.

#7: Identify a CommandBar by type

There are three types of CommandBar objects: the toolbar, the menu bar, and the popup (or shortcut menu). You can identify the type by reading the object's Type property in the form:

cb.Type

where cb refers to a CommandBar object. You can quickly illustrate this property by altering the ListCommandBarObjects() function in #6. Simply substitute the Debug statement in the For statement with the following:

Debug.Printcb.Name; vbTab, cb.Type

When run, the function will print the name and the CommandBar object type for each object in the current application. The following table identifies the three types by their VB constants and integer values:

Toolbar	msoBarTypeNormal	0
Menu bar	msoBarTypeMenuBar	1
Popup	msoBartypePopup	2

If you use the function in # 6 to document the bars in your application, you might want to identify them or even group them by type.

#8: Workaround for no Name property

The CommandBarControl object doesn't have a Name property. To identify the control, you can use the object's Caption property, Index value, or ID property (which Access assigns). Use the following procedure to list properties for all the controls on a specific CommandBar object in the Immediate window:

Function ListCommandBarControls(barname As String)
  'List command bar control names and
  'other properties.
  Dim cb As CommandBar
  Dim cbc As CommandBarControl
  Set cb = CommandBars(barname)
  For Each cbc In cb.Controls
    Debug.Print cbc.Caption; vbTab; _
     cbc.Type; vbTab; _
     cbc.Index; vbTab; _
     cbc.Tag
  Next
End Function

Remember to pass the specific bar object by name to the function as a string. For instance, run the following in the Immediate window to list the controls on the Menu Bar:

?ListcommandBarControls("menu bar")

If a name is necessary, store it in the control's Tag property. Then, read the property when the name is required.

#9: Cycle through submenus

Some CommandBarControl objects have a second layer--a submenu layer. For the most part, you'll see these in menu bars. The following procedure lists the submenu layer of the specified command bar:

Function ListSubmenus(barname As String, _
 controlname As String)
  'List submenus and
  'other properties.
  Dim cb As CommandBar
  Dim cbc As CommandBarControl
  Set cb = CommandBars(barname)
  For Each cbc In cb.Controls(controlname).Controls
    Debug.Print cbc.Caption; vbTab; _
     cbc.Type; vbTab; _
     cbc.Id
  Next
End Function

You must pass both the command bar and control name to the function as strings. For instance, enter the following in the Immediate window to list all the Edit menu submenus on the Menu Bar:

?ListSubmenus("menu bar", "edit")

Like #6 and #7, you'll find this procedure useful when documenting an application.

#10: Don't forget the ellipsis

When you see an ellipsis (...) following a menu command, you know that choosing that particular item will launch a related dialog box. For instance, the Print command on the File menu displays the ellipsis character. This lets you know that if you choose Print, the Print dialog box will appear. Most Windows-based applications follow this convention, and you should too because most of your users will recognize the visual clue. It's just one more way to make your interface seem familiar and friendly to new users. All you have to do is include the characters in the control's Caption property.

Print/View all Posts Comments on this article

10 tips for customizing menus and toolbars in your Access apps JodyGilbert | 01/03/07

Links to this article are incorrect sysadmin-tzone | 01/10/07

Can't download PDF jrporter | 05/15/07

Was able to work through this Kathy | 05/26/07

Menus on several Access formulars hermann@... | 01/08/07

Sorry... giorgio_rovelli@... | 05/10/07

Cycle through all Commandbars (menus and submenus) frz@... | 04/08/08

Even better (Excel only); frz@... | 04/08/08

Double Post frz@... | 04/08/08