Team LiB
Previous Section Next Section

CommandBar Object

The CommandBar object model (shown in Figure 8-6) is rich—it includes both built-in and custom command bars. Command bar is a generic term that refers to a menu bar, a toolbar, or a popup menu bar. CommandBar controls enable users to interface with command bars and interact with an application. The three broad classes of CommandBar controls are the CommandBarButton, CommandBarComboBox, and CommandBarPopup objects.

Click To expand
Figure 8.6: You use the CommandBar object model to customize built-in command bars and create custom command bars.

Enumerating Command Bar Elements

Enumerating command bar elements is critical to working with command bars. Enumeration provides a vehicle for learning the hierarchy of the CommandBar object. The knowledge that you gain through the following samples will help you when you modify built-in command bars and develop custom ones.

The following short procedure gives a count of command bars in an application. If no custom command bars exist, the procedure reports a count of the built-in command bars—178 for Access 2003. The number is higher if an application has custom command bars. There are 140 command bars in Access 2000 and 173 command bars in Access 2002. Access 2002 saw a substantial upgrade in its CommandBars, primarily for pivot tables and pivot charts.

Sub CountCommandBars()

MsgBox "There are " & CommandBars.Count & _
    " bars in the CommandBars collection."
   
End Sub
Note 

Access 2003 solutions that draw on command bars may not be compatible with those from Access 2000 or Access 2002, though the default file format is the same in all three versions. Note also that the CommandBars object model is a part of the Office Object Library, which updates with each version of Office (the Office 2000 version is different from the Office XP version and that version is different from the library for Office 2003). Therefore, to use programmatically specified custom command bars or customized versions of built-in command bars, plan to move all users to a single version of Access—preferably Access 2003 for its enhanced functionality.

There are three types of command bars. The Office Object Library includes the msoBarType constants to reference these as msoBarTypeNormal, msoBarTypeMenuBar, and msoBarTypePopup. You can also distinguish between built-in and custom command bars. The following procedure gives the count by type of command bar for each built-in toolbar. For a complete installation without any custom command bars, the count is one menu bar, 50 toolbars, and 127 popup bars.

Sub BuiltinCommandBarCount() 
Dim cbr1 As CommandBar
Dim iMbars As Integer
Dim iTbars As Integer
Dim iPbars As Integer
Dim iBuiltin As Integer
   
For Each cbr1 In CommandBars
    If cbr1.BuiltIn Then
        iBuiltin = iBuiltin + 1
        If cbr1.Type = msoBarTypeMenuBar Then
            iMbars = iMbars + 1
        ElseIf cbr1.Type = msoBarTypeNormal Then
            iTbars = iTbars + 1
        Else
            iPbars = iPbars + 1
        End If
    End If
Next
MsgBox "There are " & iBuiltin & " command bars. " & _
    iMbars & " is a menu bar, " & iTbars & " are toolbars, and " & _
    iPbars & " are popup bars."
   
End Sub

Listing Visible Command Bars

There are 178 built-in command bars—you probably won't want to enumerate all of them very often. However, subsets of them can be important to an application. For example, your application might want to know which command bars are visible. The following EnumerateVisibleCommandBars procedure writes a line to the Immediate window for each visible command bar. The line displays three properties for each command bar—the command bar name, type, and count of controls. A pair of nested IIf functions decode the Type property. Rather than enumerate the controls on command bars to develop a count, the procedure simply reports the command bar's Count property.

Sub EnumerateVisibleCommandBars() 
Dim cbr1 As CommandBar
   
For Each cbr1 In CommandBars
    If cbr1.Visible = True Then
        Debug.Print cbr1.Name, _
            (IIf(cbr1.Type = msoBarTypeNormal, _
            "toolbar", _
            IIf(cbr1.Type = msoBarTypeMenuBar, _
            "menu bar", "popup bar"))), _
            cbr1.Controls.Count
    End If
Next cbr1
   
End Sub

It's relatively easy to extend the previous code to enumerate the individual controls on each visible command bar. Command bars have a Controls collection, and the elements of this collection are CommandBarControl objects. The following procedure applies a CommandBar object and a CommandBarControl object while listing the captions for the controls on all visible command bars:

Sub EnumerateControlCaptions() 
Dim cbr1 As CommandBar
Dim ctl1 As CommandBarControl
   
For Each cbr1 In CommandBars
    If cbr1.Visible = True Then
        Debug.Print "Command bar name: " & cbr1.Name & _
            " and control count: "; cbr1.Controls.Count
            For Each ctl1 In cbr1.Controls
                Debug.Print cbr1.Name, ctl1.Caption
            Next ctl1
    End If
Next cbr1
   
End Sub

Listing Menu Commands

Finally, you might need to list the individual commands on a menu within a CommandBar object. This involves treating the menu as a command bar so that the commands expose themselves as controls. You can determine the name for a command bar representing a menu using the EnumerateControlCaptions procedure (or a variation of it). Ignore the ampersands (&) in a caption when specifying the command bar's name for a menu. The following pair of procedures loop through the controls on a menu. The first procedure passes a command bar name to the second procedure, which loops through the controls for that command bar. Note that the command bar's name is Help, although its caption is &Help.

Sub ListCommands()

EnumerateCommandsOnMenu ("Help")
End Sub
   
Sub EnumerateCommandsOnMenu(menuName)
Dim cbr1 As CommandBar
Dim ctl1 As CommandBarControl
   
'Set a reference to a command bar
Set cbr1 = CommandBars(menuName)
   
'Loop through the controls for that command bar
For Each ctl1 In cbr1.Controls
    Debug.Print ctl1.Caption
Next ctl1
   
End Sub

Disabling and Reenabling Command Bars and Their Controls

You can disable and restore entire command bars. The following two procedures disable the built-in menu bar (called Menu Bar) and then reenable it. To make this command bar inoperable on a form, you simply set its Enable property to False within a form event procedure. Your applications can condition the disabling of a command bar on various factors, such as a user ID.

Sub DisableMenuBar()
Dim cbr1 As CommandBar
   
For Each cbr1 In CommandBars
    If cbr1.Name = "Menu Bar" Then
        cbr1.Enabled = False
    End If
Next cbr1
   
End Sub
   
Sub EnableMenuBar()
Dim cbr1 As CommandBar
   
For Each cbr1 In CommandBars
    If cbr1.Name = "Menu Bar" Then
        cbr1.Enabled = True
    End If
Next cbr1
   
End Sub

You can also disable individual commands on a menu bar or toolbar. The first procedure in the following pair disables the View command on the Menu Bar menu bar and the Form View toolbar. This helps to secure a form's design by removing two familiar routes for switching from Form view to Design view. In addition to disabling the View control, the first procedure protects the change by setting the command bar's Protection property to msoBarNoCustomize. This setting dims the Reset button in the Customize dialog box for the Menu Bar and Form View command bars. The second procedure reenables the commands on both command bars and clears protection by setting the command bar Protection property to 0.

Sub DisableViewMenuAndControl() 
Dim ctl1 As CommandBarControl
   
'Disable and protect View menu
Set ctl1 = CommandBars("Menu Bar").Controls("View")
ctl1.Enabled = False
CommandBars("Menu Bar").Protection = msoBarNoCustomize
   
'Disable and protect View control
Set ctl1 = CommandBars("Form View").Controls("View")
ctl1.Enabled = False
CommandBars("Form View").Protection = msoBarNoCustomize
   
End Sub
   
Sub EnableViewMenuAndControl() 
Dim ctl1 As CommandBarControl
   
'Enable View menu
Set ctl1 = CommandBars("Menu Bar").Controls("View")
ctl1.Enabled = True
CommandBars("Menu Bar").Protection = 0
   
'Enable View control
Set ctl1 = CommandBars("Form View").Controls("View")
ctl1.Enabled = True
CommandBars("Form View").Protection = 0
   
End Sub

Making Invisible Command Bars Visible

Another simple but powerful manipulation you can perform is to expose a built-in menu that does not normally appear. The following procedure displays the name, type, and number of controls on each visible command bar. If the Web toolbar is not visible, the procedure resets its Visible property and leaves a record of it in the Immediate window by printing its name, type, and control count. You can make the Web toolbar disappear by resetting its Visible property to False.

Sub ShowWebBar()
Dim cbr1 As CommandBar
   
    For Each cbr1 In CommandBars
        If cbr1.Visible = True Then
            Debug.Print cbr1.Name, cbr1.Type, cbr1.Controls.Count
        ElseIf cbr1.Name = "Web" Then
            cbr1.Visible = True
            Debug.Print cbr1.Name, cbr1.Type, cbr1.Controls.Count
        End If
    Next cbr1
   
End Sub
Note 

The Chapter08.mdb file in the companion materials for this book includes a procedure named HideWebBar. This procedure makes the Web toolbar invisible again. With a collection of simple procedures like those discussed throughout the "CommandBar Object" section, you can build your own interface for managing built-in and custom command bars. This allows for tighter limits on user capabilities than the built-in toolbar management tools allow. If you want to secure changes to the CommandBars collection, you will almost surely enable manipulation of the toolbars through your own interface, which issues a subset of the built-in commands available from it.

Securing an Application by Programming Command Bars

Earlier in this chapter, I presented an example containing a custom startup form. This approach suppresses the appearance of the Database window. However, a user can easily show the Database window by using the Unhide command on the Window menu. This next sample demonstrates the code for making the Unhide command unavailable as a menu selection and preventing a user from reversing the process.

The NoHideUnhideToolbars procedure that follows has two parts. First, it iterates through the controls on the Window menu of the menu bar. The procedure assigns False to the Enabled and Visible properties of controls with the captions "&Hide" or "&Unhide…". This essentially makes the controls unavailable. If we were to stop here, the user could simply choose View, Toolbars, Customize, and then expose the command again through the Customize dialog box. Although it's possible to lock the changes to a command bar by setting its Protection property, you might want to remove the ability of end users to invoke the Customize dialog box from the View, Toolbars menu or any shortcut menu. The DisableCustomize property of the CommandBars collection enables you to do this. Simply set the property to True to prevent end users from opening the Customize dialog box from any menu. By calling the NoHideUnhide procedure from a custom startup form, you make the Hide and Unhide commands on the Window menu unavailable and you block end users from changing your settings. This can effectively lock users out of the Database window when you use it in combination with a custom startup technique.

Sub NoHideUnhide()

Dim cbr1 As CommandBar
Dim ctl1 As CommandBarControl
   
'Disable and make invisible Hide and Unhide
'commands on the Window menu
Set cbr1 = CommandBars("Window")
For Each ctl1 In cbr1.Controls
    If ctl1.Caption = "&Hide" Or _
        ctl1.Caption = "&Unhide..." Then
        ctl1.Enabled = False
        ctl1.Visible = False
    End If
Next ctl1
   
'Disable ability to open Customize dialog box
Application.CommandBars.DisableCustomize = True
   
End Sub
Note 

The Chapter08.mdb file includes a utility procedure for making the Hide and Unhide commands available again on the Window menu as well as for restoring the ability to open the Customize dialog box. In addition, another procedure named HideDBWindowAtStartupinMDB2 illustrates how to call the NoHideUnhide procedure while presenting a custom startup form. The HideDBWindowAtStartupinMDB2 utility application in Chapter08.mdb effectively presents a custom form at startup while disabling the ability of users to get to the Database window through the Window, Unhide menu item.

Adding Commands to Built-In Command Bars

Besides manipulating built-in members of the CommandBars collection, you can add custom commands to any built-in toolbar. One simple way to do this is to add a CommandBarButton object. You must know the precise name of a command bar to add a new button to it with the Add method. (Recall that you can run the EnumerateControlCaptions procedure to list the command bar names.) After adding the button, you set properties for the new CommandBarButton object so that it points at a custom procedure or function.

The NewMenuItem procedure and three related procedures shown next add new menu items to the Tools menu. The NewMenuItem procedure adds CommandBarButton objects to the end of a Tools command bar. The three related procedures let users specify whether the assistant appears as Clippit, Rocky, or F1. The new CommandBarButton objects let users invoke the procedures that control which assistant to display. Whether you like the assistant or not is immaterial. The menu commands in the sample perform a simple task that you can readily verify (by showing the assistant). The point of the sample is to demonstrate that you can add a custom menu item to any menu. In addition, you can implement a new menu item with any procedure.

Sub NewMenuItem()
Dim newItem As CommandBarButton
   
'Set reference to new control on the Tools command bar
Set newItem = CommandBars("Tools").Controls. _
    Add(Type:=msoControlButton)
'Start new group with command to invoke showClippit
With newItem
    .BeginGroup = True
    .Caption = "Show Clippit"
    .OnAction = "ShowClippit"
End With
   
'Set reference to new control on the Tools command bar
Set newItem = CommandBars("Tools").Controls. _
    Add(Type:=msoControlButton)
'Assign command to invoke showRocky
With newItem
    .Caption = "Show Rocky"
    .OnAction = "ShowRocky"
End With
   
'Set reference to new control on the Tools command bar
Set newItem = CommandBars("Tools").Controls. _
    Add(Type:=msoControlButton)
'Assign command to invoke showRocky
With newItem
    .Caption = "Show F1"
    .OnAction = "ShowF1"
End With
   
End Sub
   
Sub ShowRocky()
   
With Assistant
    .Visible = True
    .FileName = "Rocky.acs"
    .On = True
End With
   
End Sub
   
Sub ShowClippit()
   
With Assistant
    .Visible = True
    .FileName = "Clippit.acs"
    .On = True
End With
   
End Sub
   
Sub ShowF1()
   
With Assistant
    .Visible = True
    .FileName = "F1.acs"
    .On = True
End With
   
End Sub

You use the Add method for the Controls collection of a command bar to insert a new control on a built-in menu. This method takes several arguments, including a Type parameter. In addition to the button control (msoControlButton) in the sample, you can specify a simple text box (msoConrolEdit), a combo box (msoControlComboBox), and more. By default, the Add method inserts your new control at the end of a command bar, but you can override this feature so that the control appears elsewhere on the command bar. Another parameter, ID, facilitates the addition of built-in commands relative to other menus on your customized command bar.

After adding a control to a built-in command bar, you can tie it to a custom function using the OnAction property. You set the property's value equal to the name of a procedure you want your new control to invoke. The control's Caption property offers an easy way to label the new control. You can use the CopyFace and PasteFace methods to mark your custom controls. When the BeginGroup property is set to True, a control appears on a command bar with a divider line before it. The sample sets this property to True for the first of the three custom controls, but it leaves it at the default value of False for the remaining two controls.

As you refine custom applications, you'll sometimes want to remove custom controls on built-in menus. You can do this using the Reset method. The following procedure clears any custom controls on the Tools command bar:

Sub removeMenuItem()
CommandBars("Tools").Reset
End Sub

Creating Custom Command Bars

Creating a custom command bar involves at least three steps:

  1. Adding a new command bar to your application. It will be blank when your code initially inserts it.

  2. Positioning controls on the command bar. This is similar to placing controls on a built-in command bar.

  3. Setting the Visible property of the command bar to True when you want to show it. You can also let users expose your custom command bar using standard features (such as the Customize dialog box).

The following two procedures add a custom command bar with a single button control to make Rocky appear as the assistant. The NewCommandBarAndButton procedure passes off the first two steps of creating command bars to the procedure AddShowAssistantsAndRocky. Placing these steps in a separate procedure has advantages for a subsequent sample. The AddShowAssistantsAndRocky procedure names the new custom command bar Show Assistants. Next, the procedure adds a custom control. When you specify controls for custom command bars, you must assign a value to the Style property as well as to the other properties that you set with built-in command bars. Failing to do so in the procedure AddShowAssistantsAndRocky can cause the button on the command bar to appear blank.

Sub NewCommandBarAndButton() 
On Error GoTo CBarBtnTrap
Dim cbr1 As CommandBar
Dim cbr1btn1 As CommandBarButton
Dim cbr1Name As String
   
'Add command bar to show Rocky
AddShowAssistantsAndRocky
   
'Make command bar visible
Set cbr1 = CommandBars("Show Assistants")
cbr1.Visible = True
   
CBarBtnExit:
Exit Sub
   
CBarBtnTrap:
Debug.Print Err.Number; Err.Description
Resume CBarBtnExit
   
End Sub
   
Sub AddShowAssistantsAndRocky() 
Dim cbr1 As CommandBar
Dim cbr1btn1 As CommandBarButton
   
'Add a command bar named Show Assistants
Set cbr1 = CommandBars.Add("Show Assistants", _
    msoBarTop, , True)
   
'Add a button control to the command bar
Set cbr1btn1 = cbr1.Controls _
    .Add(msoControlButton, , , , True)
'Set button properties
With cbr1btn1
    .Caption = "Show Rocky"
    .BeginGroup = True
    .OnAction = "ShowRocky"
    .Style = msoButtonCaption
End With
   
End Sub

After the NewCommandBarAndButton procedure regains control, it sets the control's Visible property to True. Without this step, the only way a user can view the new custom command bar is by explicitly showing it (for instance, by right-clicking a command bar and selecting the name of the command bar you want to show). The error-trapping logic in the NewCommandBarAndButton procedure allows the application to invoke the procedure even when the command bar is already present. Without the error-trapping logic, the addShowAssistantsAndRocky procedure generates a fatal error when it tries to add a command bar that already exists. Because this error is not critical—after all, the command bar is there already—it's reasonable to ignore it.

Modifying Custom Command Bars

The following three procedures add new controls to an existing custom command bar. They also reveal another approach to handling the problem of an existing command bar. The AddCbrBtns procedure inserts another pair of buttons on the Show Assistants command bar created in the previous sample. If that command bar does not already exist, this procedure is smart enough to run the AddShowAssistantsAndRocky procedure. AddCbrBtns conditionally calls the procedure that creates the Show Assistants command bar based on the return value of the DoesCbrExist function procedure. This function procedure checks for the existence of a command bar. Whether or not the Show Assistants command bar exists, the initial If…Then…Else statement sets a reference to it. The rest of the procedure adds two more buttons to the command bar. AddCbrBtns closes by making the command bar visible if it isn't already.

Sub MoreButtons()
    AddCbrBtns "Show Assistants"
End Sub
   
Sub AddCbrBtns(cbrName As String)
Dim cbr1 As CommandBar
Dim cbr1btn1 As CommandBarButton
   
'Optionally create Show Assistants command bar.
'Reference it with a variable.
If Not doesCbrExist(cbrName) Then
    AddShowAssistantsAndRocky
    Set cbr1 = CommandBars(cbrName)
Else
    Set cbr1 = CommandBars(cbrName)
End If
   
'Add a new button to Show Assistants command bar
Set cbr1btn1 = cbr1.Controls _
    .Add(msoControlButton, , , , True)
'Set properties for button to show Clippit
With cbr1btn1
    .Caption = "Show Clippit"
    .OnAction = "ShowClippit"
    .Style = msoButtonCaption
End With
   
'Add a new button to Show Assistants command bar
Set cbr1btn1 = cbr1.Controls _
    .Add(msoControlButton, , , , True)
'Set properties for button to show F1
With cbr1btn1
    .Caption = "Show F1"
    .OnAction = "ShowF1"
    .Style = msoButtonCaption
End With
   
'Make the Show Assistants command bar visible
If Not cbr1.Visible = True Then cbr1.Visible = True
   
End Sub
   
Function doesCbrExist(cbrName As String) As Boolean
Dim cbr1 As CommandBar
   
doesCbrExist = False
For Each cbr1 In CommandBars
    If cbr1.Name = cbrName Then
        doesCbrExist = True
    End If
Next cbr1
   
End Function 

Creating Popup Command Bars

The first sample procedure that follows enables a combo box control on a custom command bar and makes the command bar a popup menu bar. Figure 8-7 shows the behavior of the popup menu bar on a form. You click anywhere on the form to bring up a custom command bar with a single control. This control is a combo box with entries for selecting the Clippit, Rocky, or F1 assistant. The process starts with a click event for the form's Detail section. The next three procedures implement the sample depicted in Figure 8-7.

Click To expand
Figure 8.7: A custom popup menu bar with a combo box control. You click anywhere on the form to open the custom menu bar.
'From module behind form with popup toolbar
Private Sub Detail_Click() 
    ShowAndProcessComboBox
End Sub
   
'From standard module in Access database file
'with form containing popup toolbar
Sub ShowAndProcessComboBox()
Dim cbr1 As CommandBar
   
'Call from click event in form
   
    If doesCbrExist("Custom1") Then
        CommandBars("Custom1").ShowPopup
    Else
        CreateAndShowPopUpMenu
    End If
   
End Sub
   
Sub CreateAndShowPopUpMenu() 
Dim cbr1 As CommandBar
   
'Add command bar named Custom1
    Set cbr1 = CommandBars _
        .Add(Name:="Custom1", Position:=msoBarPopup, Temporary:=True)
   
    With cbr1
        .Controls.Add Type:=msoControlComboBox
        With .Controls(1)
            .Style = msoComboLabel
            .Caption = "Pick an Assistant."
            .AddItem "Show Clippit"
            .AddItem "Show Rocky"
            .AddItem "Show F1"
            .OnAction = "processComboBoxChoice"
        End With
    End With
   
    cbr1.ShowPopup
   
End Sub
   
Sub ProcessComboBoxChoice() 
   
'Decode selected item and implement corresponding method
    Select Case _
        CommandBars("custom1").Controls(1).ListIndex
        Case 1
            ShowClippit
        Case 2
            ShowRocky
        Case 3
            ShowF1
    End Select
    
End Sub

The first procedure is the event procedure behind the form. It calls ShowAndProcessComboBox, a procedure that resides in a standard module. This procedure determines whether the Custom1 command bar already exists. If the command bar exists, the procedure invokes the ShowPopup method to display the command bar as a popup menu bar. Otherwise, it creates the Custom1 command bar with a call to CreateAndShowPopUpMenu. As the name of this third procedure implies, it creates the custom command bar just before displaying it as a popup menu bar. The CreateAndShowPopUpMenu procedure is compact, but it uses interesting techniques. First, it contains nested With…End With statements. The outer statement adds a new member to the CommandBars collection, and the inner one adds a control to that member. The property assignments within the inner With…End With statement specify a combo box style for the control, define the elements in the combo box list, and denote a procedure, ProcessComboBoxChoice, that fires after a selection from the combo box. This final procedure uses a Select Case statement based on the selected element from the combo box list to invoke one of three custom procedures that display an assistant.

Deleting Custom Command Bars

If you build custom command bars, you'll eventually need to remove one or more of them within an application. The following sample does this by looping through all the command bars to find the custom ones—those with a BuiltIn property of False. When the procedure finds a custom command bar, it asks the user whether it should delete the command bar. If the user replies Yes, the procedure deletes that command bar and adds one to the count of deleted command bars. In any event, the procedure increments a variable that tallies custom command bars.

Sub DeleteCustomCbr()
Dim cbr1 As CommandBar, delFlag As Boolean
Dim delBars As Integer, cusBars As Integer
    
'Not necessary to initialize delFlag, delBars, or
'cusBars because their default values (False and 0)
'are OK
    
'Conditionally delete custom menu bars
    For Each cbr1 In CommandBars
        If (cbr1.BuiltIn = False) Then
            If MsgBox("Are you sure that you want to " & _
                "delete the " & cbr1.Name & " command bar?", _
                vbYesNo, _
                "Programming Microsoft Access 2003") = _
                    vbYes Then
                    cbr1.Delete
                    delFlag = True
                    delBars = delBars + 1
            End If
            cusBars = cusBars + 1
        End If
    Next cbr1
    
'Report outcome of command bar enumeration
    If Not delFlag Then
        If cusBars > 0 Then
            MsgBox "No custom command bars deleted " & _
                "out of a total of " & cusBars & ".", _
                vbInformation, _
                "Programming Microsoft Access 2003"
        Else
            MsgBox "No custom command bars.", vbInformation, _
                "Programming Microsoft Access 2003"
        End If
    Else
        MsgBox delBars & " custom command bar(s) deleted.", _
            vbInformation, _
            "Programming Microsoft Access 2003"
    End If
   
End Sub

The DeleteCustomCbr procedure closes by presenting one of three possible statements based on the number of deletions and the number of custom command bars. A pair of nested If…Then…Else statements handles the routing to the correct message box statement. If there are no deletions but at least one custom command bar, the statement displays a message reporting that no custom command bars were deleted and showing the total number of custom command bars. If there are no deletions and no custom command bars, the procedure presents a message to that effect. Finally, if the procedure deleted any command bars, the message box reports that number.


Team LiB
Previous Section Next Section