Customizing Applications with CommandBar Objects

Customizing Applications with CommandBar Objects

Office 2003 applications use CommandBar objects to create menubars, pop-up menus, toolbar buttons, and toolbar combo boxes. To gain VBA programming access to CommandBar objects, your database must include a reference to the Microsoft Office 11.0 Object Library, Mso11.dll. Northwind.mdb includes a reference to Mso11.dll. If it's missing from your VBA project, choose Tools, References, scroll to the Microsoft Office 11.0 Object Library entry, and mark the check box (see Figure 28.20).

Figure 28.20. Add a reference to Mso11.dll to enable use of the CommandBar object in your VBA project.

Mso11.dll includes other objects that are common to Office 2003, such as the Balloon object of the Office Assistant object and the DocumentProperty object for files. Figure 28.21 shows the Microsoft Office Objects help topic that displays some of the collections and objects exposed by Mso11.dll, which differ considerably from the Office 2000 model. (Type Microsoft Office Objects in the Visual Basic Editor's Ask a Question box and click the Microsoft Office Objects link to display the help topic.)

Figure 28.21. The Office 2003 object model includes objects for all Office members, such as the OfficeDataSource-Object. The CommandBars collection is missing from the Help topic, but it appears in Object Browser.

With a reference to Mso11.dll, Office 2003 object classes appear in Object Browser's window when you select the Office library (see Figure 28.22). Selecting a class or member of the class and then clicking Object Browser's Help (?) button displays the Microsoft Office Visual Basic Help topic for most selections. Using Object Browser's Help button is the easiest way to obtain the VBA syntax for programming specific Office objects.

Figure 28.22. Adding a reference to Mso11.dll adds an Office entry to Object Browser's Project/Library list. Office classes include application objects common to Office 2003 members.

The Immediate window is useful for gaining familiarity with programming Office objects. Figure 28.23 shows how to obtain property values of the CommandBars collection, CommandBar objects, and the Control objects of CommandBars. The Access 2003 version of Northwind.mdb has 178 CommandBars, an increase from 173 in Access 2002, 141 in Access 2000, and 104 in Access 97. Unlike most other Access collections that begin with an index value of 0, the first member of Office collections has an index value of 1. The first CommandBar of Office XP members is the TaskPane. The Application preface in the first statement of Figure 28.23 is optional; the Application object is assumed when referring to top-level Access objects.

Figure 28.23. The Immediate window lets you explore the properties of Access 2003's built-in toolbars and menu bars. The second CommandBar is the toolbar that appears when the Database window has the focus.

Note

The Northwind.mdb database of Access 2000 and earlier specified a custom menu bar (NorthwindCustomMenuBar), which added a Show Me command to the main menu. Access 2003 dispenses with the custom menubar and it's associated help topic.

Tip

To view the members of the Access Application class, open the Access library in Object Browser and select Application in the Classes list.

The following simple VBA subprocedure, added to a temporary new module, Module1, iterates the CommandBars collection and prints the Name and Visible property values of each CommandBar in the Immediate window:

 Sub PrintCommandBars()    Dim msoBar As CommandBar    For Each msoBar In CommandBars       Debug. Print msoBar.Name, msoBar.Visible    Next msoBar End Sub 

Figure 28.24 shows the preceding subprocedure and the first 15 built-in CommandBars of Access 2003.

Figure 28.24. The Immediate window displays the Name and Visible property values of the built-in CommandBar objects.

You add a new CommandBar object by applying the Add method to the CommandBars collection, setting the properties of the new CommandBar object, and adding members to the CommandBar object's Controls collection. For example, the code in Listing 28.2 adds a command button to the Form View command bar when you open the frmCommandWiz form of the Events28.mdb sample database.

Listing 28.2 Add a Command Button as a Member of the Controls Collection of the Form View CommandBar Object

 Private Sub Form_Load()    'Add a button that opens the Customers form to the    'Form View toolbar at the right of the View menu    Dim cmbFormView As CommandBar    Dim cbcCustData As CommandBarControl    Dim intCtr As Integer    'Create the CommandBar object    Set cmbFormView =CommandBars("Form View")    'Test to see if the button exists    For intCtr = 1 To cmbFormView.Controls.Count       Set cbcCustData =cmbFormView.Controls(intCtr)       If cbcCustData.Caption = "Customer Data" Then           'The control already exists, don't add another           Exit Sub       End If    Next intCtr    'If the button isn't present, add it in position 2    Set cbcCustData = cmbFormView.Controls.Add(msoControlButton, , , 2)    With cbcCustData       .Caption = "Customer Data"       .FaceId = 209       .OnAction = "=DisplayCustomerData"       .TooltipText = "Click to display customer data    End With End Sub 

The OnAction property of the cbcCustData command button calls the DisplayCustomerData function, which must have Public scope. The code of the DisplayCustomerData function is identical to that of the subDisplayCustomerData_Click subprocedure, which is described in the earlier "Adding a Button to Display a Specific Record on Another Form" section. Figure 28.25 shows the new button added to the right of the View button with its ToolTip activated.

Figure 28.25. Clicking the added command button of the Form View toolbar performs the same task as clicking the Display Customer Data button of frmCommandWiz.

Tip

To remove the added button from the Form View toolbar, choose View, Toolbars, Customize, drag the button off the command bar, and drop it anywhere.

A full exposition of VBA programming of custom CommandBar objects is beyond the scope of this book. Most of the developer-level books for Access 2003 listed in the "Bibliography" section of the Introduction to this book cover custom CommandBar programming in detail.