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.
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
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
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
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
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
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. |
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
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 a custom command bar involves at least three steps:
Adding a new command bar to your application. It will be blank when your code initially inserts it.
Positioning controls on the command bar. This is similar to placing controls on a built-in command bar.
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.
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
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.
'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.
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.