Responding to CommandBar Events

When the user clicks a menu item or toolbar button or selects an item from a drop-down list on a toolbar, specific code is executed, depending on the control, in order to respond to the event. An event is something that takes place, such as a click or a change, that causes an object to react. In the case of menus and toolbars, the Click event is triggered when the user clicks a menu item or toolbar button, and the Change event is triggered when the user selects an item in a drop-down list or changes the text in an edit control. You write code that connects to these events and processes them in one way or another.

The Click and Change events are new members of the CommandBar object model in Microsoft Office 2000. Events allow you to connect an event procedure to an action, such as a click on a command bar button or a change in the item selected in a drop-down list. Because most menu and toolbar controls are buttons (whether they display both an image and text, or text or an image only), the Click event on the CommandBarButton object is the most common event. In a Click event procedure, you commonly place code that displays a custom dialog box when a command bar button is clicked.

The Change event on the CommandBarComboBox object is the next most common event. In addition to a menu item or toolbar button, you'll commonly see drop-down list or combo box controls in the toolbar set. These controls can take the form of an edit list, a combo box, or just a drop-down list. On the Standard toolbar in Word, Excel, and PowerPoint, you'll find the Zoom control, while on the Formatting toolbar you'll find the Font and Font Size combo boxes.

Before setting up the event procedure, you also need to set the Tag property on the custom command to a unique string you define. In the Quick Guide "Setting Up the Button Click Event" at the beginning of this chapter, you set the Tag property to "BrButton." Setting the Tag property ensures that no matter how many copies of a custom control exist in a command bar set, the Click or Change event procedure will always be executed. As long as your code connects to one copy of the custom control, Office uses the Tag property to uniquely identify the control and connect the control to the appropriate event procedure.

For example, after your code creates a custom menu command with the Tag property set to a unique string you've defined, the user can click Customize on the Tools menu so that the Office application is in command Bar Customize mode. Holding the CTRL key, the user can copy your custom control to another menu or toolbar. Because the Tag property uniquely identifies all copies of the control, setting up an event procedure to just one copy of the control ensures the event procedure is always executed, no matter which copy of the control is clicked or changed.

The Tag property is also useful when you want to search for all copies of a custom menu command or toolbar button or any other command bar control you can add through code. The last section of this chapter discusses how to use the FindControls method and why the Tag property is important when using the FindControls method.

You can cancel the built-in behavior of a command like the Open command on the File menu and bring up your own dialog box. The Open command is in two places in the command bar set in Word, Excel, PowerPoint, and Access. You can connect to just one copy of the Open command and always get the event no matter which command the user clicked. This is the same way custom commands behave. That is, Office uses the built-in Id property of the command to sink an event. Then no matter where the button is clicked, if it has the associated Id, Office will execute the command's event sink. By setting the CancelDefault argument passed into the Click event procedure to True, you cancel a built-in command's default behavior. To do this, follow the steps below.

1. Add the following procedure to a standard code module and add the declaration to the top of the same code module.

Dim CmdBarEvents As New Class1 Sub OverrideButton() Dim ctlBtn As Office.CommandBarButton Set ctlBtn = _ CommandBars("File").FindControl(Id:=23) Set CmdBarEvents.OpenBtnCtrl = ctlBtn End Sub

The FindControl method (not to be confused with the FindControls method, described at the end of this chapter) returns a control that fits the search criteria. In this case, the search criteria passed into the FindControl method is set to find the control with the built-in identifier value of 23. This represents the Open control.

2. Insert a new class module and add the following code. Make sure you enter the declaration of the public-level variable OpenBtnCtrl at the top of the class module.

Public WithEvents OpenBtnCtrl As _ Office.CommandBarButton Private Sub OpenBtnCtrl_Click(ByVal Ctrl As _ Office.CommandBarButton, CancelDefault As Boolean) CancelDefault = True MsgBox Ctrl.Caption & " was cancelled" End Sub

If you are inserting this code in one of the examples listed earlier in this chapter, the default name of the inserted class module will be Class2. You need to ensure that the name of this class module is the same as the data type name in the CmdBarEvents variable declaration entered in step 1. For example, if the inserted class module is named Class2, change the declaration entered in step 1 to:

Dim CmdBarEvents As New Class2

3. Run the procedure OverrideButton, switch to the Office application, and click the Open button on the File menu or the Standard toolbar.

When you click the Open command on the File menu in Word, Excel, or PowerPoint, you see the Open dialog box. However, in the event procedure added in step 3, you set CancelDefault to True. Clicking the Open button on the File menu or Standard toolbar therefore will first display the message box "&Open was cancelled" and then nothing else happens; the Open dialog box isn't displayed. To prevent the event procedure from executing and canceling the built-in behavior of the Open command, switch to the Visual Basic Editor and click Reset on the Run menu.