The CommandBar Object

The CommandBar object model (shown in Figure 8-9) is very 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 CommandBarButton, CommandBarComboBox, and CommandBarPopup objects.

Figure 8-9. 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 is 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 there are no custom command bars, the procedure reports a count of the built-in command bars, 140. The number would be higher if an application had custom command bars.

 Sub countCommandBars()     MsgBox "There are " & CommandBars.Count & _         " bars in the CommandBars collection." End Sub 

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.

 Sub builtinCommandBarCount() Dim cbr1 As CommandBar Dim iMbars As Integer, iTbars As Integer Dim iPbars As Integer, 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."s End Sub 

Listing visible command bars

Since there are 140 built-in command bars, you probably will not 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 lines display three properties for each command bar— the command bar name, type, and count of controls. A pair of nested Immediate If 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 is relatively easy to extend the above 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.Captions                 Next ctl1         End If     Next cbr1 End Sub 

Listing menu commands

Finally, you might need to list the individual commands on a menu. 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). 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.

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

Manipulating Built-In Command Bars

You can modify built-in command bars in several ways. The following sections explore some of these.

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 grays 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.

 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 'Enable View Control.     Set ctl1 = CommandBars("Form View").Controls("View")     ctl1.Enabled = True     End Sub 

Making invisible command bars visible

Another simple but powerful manipulation you can carry out is exposing a built-in menu that does not normally show. 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 

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 below add new menu items. 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 lets users invoke the procedures that control which assistant to display.

 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 from other menus to your custom 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.

Restoring command bars

As you refine custom applications, you will 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. 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 the other property values 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 CommandBar 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. Since this error is not critical (after all, the command bar is there already), it is 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 is not already visible.

 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 below enables a combo box control on a custom command bar and makes the command bar a popup menu bar. Figure 8-10 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 code the sample depicted in Figure 8-10.

Figure 8-10. A custom popup menu bar with a combo box control. You click anywhere on the form to open the custom menu bar.

 Private Sub Detail_Click()     ShowAndProcessComboBox End Sub 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:=msoControlComboBoxS         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() Dim caseValue As Integer     '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 uses nested With…End statements. The outer one 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 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 will 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 Built-in property of False. When the procedure finds a custom command bar, it asks the user whether to delete the command bar. If the user replies Yes, it deletes that command bar and adds one to the count of deleted command bars. In any event, the procedure adds one to 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 2000") = _                     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 2000"         Else             MsgBox "No custom command bars.", vbInformation, _                 "Programming Microsoft Access 2000"         End If     Else         MsgBox delBars & " custom command bar(s) deleted.", _             vbInformation, "Programming Microsoft Access 2000"     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 there is at least one custom command bar, the statement displays a message reporting that no custom command bars were deleted and displaying 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.