Microsoft Access VBA Programming for the Absolute Beginner

In the previous two chapters, you saw how to interact with VBA through the use of forms and reports. However, you can interact through the use of command bars also. In this chapter, you will learn what command bars are and how to create and use one. We will also look at Access’s built-in command bars and work with menus and submenus.

Understanding Command Bars

The term command bar is a catch-all term that encompasses toolbars and menus. As a matter of fact, the collection of all the toolbars, menus, and shortcut menus is referred to as the CommandBars collection. This includes the built-in command bars, as well as any customized ones you may create. The CommandBars collection is contained within the larger Application object.

To give you an idea how large Access 2003 is, it contains 178 command bars. This includes one menu bar, 126 shortcut menus, and 51 toolbars. Not all of the toolbars can be customized, as you will soon see.

Let’s write a little bit of code to show which command bars are available to you. Before you begin, though, make sure that a reference to the Office object model has been added to your project by selecting the References item from the Tools menu of the VBA Editor window. When the References dialog box opens, check the box for the Microsoft Office x.0 Object Library if it is not already checked. (Checked items appear at the beginning of the Available References list box and are followed by unchecked items.) Then add the following code:

Sub listCommandBars() Dim comBar As CommandBar Dim comBarType As String For Each comBar In CommandBars Select Case comBar.Type Case msoBarTypeNormal comBarType = "Toolbar" Case msoBarTypeMenuBar comBarType = "Menu Bar" Case msoBarTypePopup comBarType = "Shortcut" End Select Debug.Print comBar.Index, comBar.Name, comBarType, comBar.Visible Next End Sub

This will yield results in the Immediate window resembling those shown in Figure 14-1.

Figure 14-1: The command bar listing in the Immediate window

As you may have guessed, the CommandBar object is part of the Office library. We have printed out the properties:

• Index The position of the command bar within the entire CommandBars collection

• Name The name of the command bar

• Visible Whether the command bar is visible or not

The Type property will return what type of msoBar it is (“mso” stands for Microsoft Office). If you look in the Object Browser and search for the msoBarType, you will get the results shown here:

Each type is itself a separate object. Understanding this is an important part of working with command bars in VBA.

Each of these command bar types can be called up using either their name or a numeric reference.

• msoBarTypeNormal This is a toolbar and has a numeric equivalent of 0.

• msoBarTypeMenuBar This is a menu bar and has a numeric equivalent of 1.

• msoBarTypePopup This is a shortcut menu and has a numeric equivalent of 2.