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.

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 .

 SubCountCommandBars() MsgBox "Thereare " &CommandBars.Count&_  " barsintheCommandBarscollection."     EndSub

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.

 SubBuiltinCommandBarCount()  Dimcbr1AsCommandBar DimiMbarsAsInteger DimiTbarsAsInteger DimiPbarsAsInteger DimiBuiltinAsInteger     ForEachcbr1InCommandBars Ifcbr1.BuiltInThen iBuiltin=iBuiltin+1 Ifcbr1.Type=msoBarTypeMenuBarThen iMbars=iMbars+1 ElseIfcbr1.Type=msoBarTypeNormalThen iTbars=iTbars+1 Else iPbars=iPbars+1 EndIf EndIf Next MsgBox "Thereare " &iBuiltin& " commandbars. " &_ iMbars& " isamenubar, " &iTbars& " aretoolbars,and " &_ iPbars& " arepopupbars."     EndSub

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.

 SubEnumerateVisibleCommandBars()  Dimcbr1AsCommandBar     ForEachcbr1InCommandBars Ifcbr1.Visible=TrueThen Debug.Printcbr1.Name,_ (IIf(cbr1.Type=msoBarTypeNormal,_  "toolbar",_ IIf(cbr1.Type=msoBarTypeMenuBar,_  "menubar", "popupbar"))),_ cbr1.Controls.Count EndIf Nextcbr1     EndSub

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:

 SubEnumerateControlCaptions()  Dimcbr1AsCommandBar Dimctl1AsCommandBarControl     ForEachcbr1InCommandBars Ifcbr1.Visible=TrueThen Debug.Print "Commandbarname: " &cbr1.Name&_  " andcontrolcount: ";cbr1.Controls.Count ForEachctl1Incbr1.Controls Debug.Printcbr1.Name,ctl1.Caption Nextctl1 EndIf Nextcbr1     EndSub

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.

 SubListCommands() EnumerateCommandsOnMenu("Help") EndSub     SubEnumerateCommandsOnMenu(menuName) Dimcbr1AsCommandBar Dimctl1AsCommandBarControl     'Setareferencetoacommandbar Setcbr1=CommandBars(menuName)     'Loopthroughthecontrolsforthatcommandbar ForEachctl1Incbr1.Controls Debug.Printctl1.Caption Nextctl1     EndSub

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.

 SubDisableMenuBar() Dimcbr1AsCommandBar     ForEachcbr1InCommandBars Ifcbr1.Name= "MenuBar" Then cbr1.Enabled=False EndIf Nextcbr1     EndSub     SubEnableMenuBar() Dimcbr1AsCommandBar     ForEachcbr1InCommandBars Ifcbr1.Name= "MenuBar" Then cbr1.Enabled=True EndIf Nextcbr1     EndSub

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.

 SubDisableViewMenuAndControl()  Dimctl1AsCommandBarControl     'DisableandprotectViewmenu Setctl1=CommandBars("MenuBar").Controls("View") ctl1.Enabled=False CommandBars("MenuBar").Protection=msoBarNoCustomize     'DisableandprotectViewcontrol Setctl1=CommandBars("FormView").Controls("View") ctl1.Enabled=False CommandBars("FormView").Protection=msoBarNoCustomize     EndSub     SubEnableViewMenuAndControl()  Dimctl1AsCommandBarControl     'EnableViewmenu Setctl1=CommandBars("MenuBar").Controls("View") ctl1.Enabled=True CommandBars("MenuBar").Protection=0     'EnableViewcontrol Setctl1=CommandBars("FormView").Controls("View") ctl1.Enabled=True CommandBars("FormView").Protection=0     EndSub

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 .

 SubShowWebBar() Dimcbr1AsCommandBar     ForEachcbr1InCommandBars Ifcbr1.Visible=TrueThen Debug.Printcbr1.Name,cbr1.Type,cbr1.Controls.Count ElseIfcbr1.Name= "Web" Then cbr1.Visible=True Debug.Printcbr1.Name,cbr1.Type,cbr1.Controls.Count EndIf Nextcbr1     EndSub

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.

 SubNoHideUnhide() Dimcbr1AsCommandBar Dimctl1AsCommandBarControl     'DisableandmakeinvisibleHideandUnhide 'commandsontheWindowmenu Setcbr1=CommandBars("Window") ForEachctl1Incbr1.Controls Ifctl1.Caption= "&Hide" Or_ ctl1.Caption= "&Unhide..." Then ctl1.Enabled=False ctl1.Visible=False EndIf Nextctl1     'DisableabilitytoopenCustomizedialogbox Application.CommandBars.DisableCustomize=True     EndSub

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.

 SubNewMenuItem() DimnewItemAsCommandBarButton     'SetreferencetonewcontrolontheToolscommandbar SetnewItem=CommandBars("Tools").Controls._ Add(Type:=msoControlButton) 'StartnewgroupwithcommandtoinvokeshowClippit WithnewItem .BeginGroup=True .Caption= "ShowClippit" .OnAction= "ShowClippit" EndWith     'SetreferencetonewcontrolontheToolscommandbar SetnewItem=CommandBars("Tools").Controls._ Add(Type:=msoControlButton) 'AssigncommandtoinvokeshowRocky WithnewItem .Caption= "ShowRocky" .OnAction= "ShowRocky" EndWith     'SetreferencetonewcontrolontheToolscommandbar SetnewItem=CommandBars("Tools").Controls._ Add(Type:=msoControlButton) 'AssigncommandtoinvokeshowRocky WithnewItem .Caption= "ShowF1" .OnAction= "ShowF1" EndWith     EndSub     SubShowRocky()     WithAssistant .Visible=True .FileName= "Rocky.acs" .On=True EndWith     EndSub     SubShowClippit()     WithAssistant .Visible=True .FileName= "Clippit.acs" .On=True EndWith     EndSub     SubShowF1()     WithAssistant .Visible=True .FileName= "F1.acs" .On=True EndWith     EndSub

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:

 SubremoveMenuItem() CommandBars("Tools").Reset EndSub

Creating Custom Command Bars

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.

 SubNewCommandBarAndButton()  OnErrorGoToCBarBtnTrap Dimcbr1AsCommandBar Dimcbr1btn1AsCommandBarButton Dimcbr1NameAsString     'AddcommandbartoshowRocky AddShowAssistantsAndRocky     'Makecommandbarvisible Setcbr1=CommandBars("ShowAssistants") cbr1.Visible=True     CBarBtnExit: ExitSub     CBarBtnTrap: Debug.PrintErr.Number;Err.Description ResumeCBarBtnExit     EndSub     SubAddShowAssistantsAndRocky()  Dimcbr1AsCommandBar Dimcbr1btn1AsCommandBarButton     'AddacommandbarnamedShowAssistants Setcbr1=CommandBars.Add("ShowAssistants",_ msoBarTop,,True)     'Addabuttoncontroltothecommandbar Setcbr1btn1=cbr1.Controls_ .Add(msoControlButton,,,,True) 'Setbuttonproperties Withcbr1btn1 .Caption= "ShowRocky" .BeginGroup=True .OnAction= "ShowRocky" .Style=msoButtonCaption EndWith     EndSub

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.

 SubMoreButtons() AddCbrBtns "ShowAssistants" EndSub     SubAddCbrBtns(cbrNameAsString) Dimcbr1AsCommandBar Dimcbr1btn1AsCommandBarButton     'OptionallycreateShowAssistantscommandbar. 'Referenceitwithavariable. IfNotdoesCbrExist(cbrName)Then AddShowAssistantsAndRocky Setcbr1=CommandBars(cbrName) Else Setcbr1=CommandBars(cbrName) EndIf     'AddanewbuttontoShowAssistantscommandbar Setcbr1btn1=cbr1.Controls_ .Add(msoControlButton,,,,True) 'SetpropertiesforbuttontoshowClippit Withcbr1btn1 .Caption= "ShowClippit" .OnAction= "ShowClippit" .Style=msoButtonCaption EndWith     'AddanewbuttontoShowAssistantscommandbar Setcbr1btn1=cbr1.Controls_ .Add(msoControlButton,,,,True) 'SetpropertiesforbuttontoshowF1 Withcbr1btn1 .Caption= "ShowF1" .OnAction= "ShowF1" .Style=msoButtonCaption EndWith     'MaketheShowAssistantscommandbarvisible IfNotcbr1.Visible=TrueThencbr1.Visible=True     EndSub     FunctiondoesCbrExist(cbrNameAsString)AsBoolean Dimcbr1AsCommandBar     doesCbrExist=False ForEachcbr1InCommandBars Ifcbr1.Name=cbrNameThen doesCbrExist=True EndIf Nextcbr1     EndFunction

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.

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.

 'Frommodulebehindformwithpopuptoolbar PrivateSubDetail_Click()  ShowAndProcessComboBox EndSub     'FromstandardmoduleinAccessdatabasefile 'withformcontainingpopuptoolbar SubShowAndProcessComboBox() Dimcbr1AsCommandBar     'Callfromclickeventinform     IfdoesCbrExist("Custom1")Then CommandBars("Custom1").ShowPopup Else CreateAndShowPopUpMenu EndIf     EndSub     SubCreateAndShowPopUpMenu()  Dimcbr1AsCommandBar     'AddcommandbarnamedCustom1 Setcbr1=CommandBars_ .Add(Name:="Custom1",Position:=msoBarPopup,Temporary:=True)     Withcbr1 .Controls.AddType:=msoControlComboBox With.Controls(1) .Style=msoComboLabel .Caption= "PickanAssistant." .AddItem "ShowClippit" .AddItem "ShowRocky" .AddItem "ShowF1" .OnAction= "processComboBoxChoice" EndWith EndWith     cbr1.ShowPopup     EndSub     SubProcessComboBoxChoice()      'Decodeselecteditemandimplementcorrespondingmethod SelectCase_ CommandBars("custom1").Controls(1).ListIndex Case1 ShowClippit Case2 ShowRocky Case3 ShowF1 EndSelect EndSub

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.

 SubDeleteCustomCbr() Dimcbr1AsCommandBar,delFlagAsBoolean DimdelBarsAsInteger,cusBarsAsInteger 'NotnecessarytoinitializedelFlag,delBars,or 'cusBarsbecausetheirdefaultvalues(Falseand0) 'areOK 'Conditionallydeletecustommenubars ForEachcbr1InCommandBars If(cbr1.BuiltIn=False)Then IfMsgBox("Areyousurethatyouwantto " &_  "deletethe " &cbr1.Name& " commandbar?",_ vbYesNo,_  "ProgrammingMicrosoftAccess2003")=_ vbYesThen cbr1.Delete delFlag=True delBars=delBars+1 EndIf cusBars=cusBars+1 EndIf Nextcbr1 'Reportoutcomeofcommandbarenumeration IfNotdelFlagThen IfcusBars>0Then MsgBox "Nocustomcommandbarsdeleted " &_  "outofatotalof " &cusBars& ".",_ vbInformation,_  "ProgrammingMicrosoftAccess2003" Else MsgBox "Nocustomcommandbars.",vbInformation,_  "ProgrammingMicrosoftAccess2003" EndIf Else MsgBoxdelBars& " customcommandbar(s)deleted.",_ vbInformation,_  "ProgrammingMicrosoftAccess2003" EndIf     EndSub

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.