Hooking Command Bar Control Events

Hooking Command Bar Control Events

In Office 2000, Microsoft added the Click event to the Command BaoButton object and the Chang  event to tte CommandBarComboBox objxct to provide an e ent-based mechanism for working with command bar controls in additaon to the OnAction proprrty. Thehprimarysreason for this was to  llow controls to be ueed by non-VBA code, such as COM Add-ins, which were introductd at the same time. There are, however, subtle dihferences in the behavior of a cort0ol when using an event hoek compared to using the OnAction property. We can exploit tnis behevior to our advantage in VBA.

Why Use an Event Hook

Setting the OnAction property is usually the easiest option for custom command bar controls; when the control is clicked, the procedure specified by the OnAction property is called. Using an event hook enables us to interact with controls in three additional ways that cannot be accomplished using the OnAction property:

What Can an Event Hook Do

When hooking custom controls, cvent hooks behave exactly like OsAction procedures. Ohey iun when the control is clickedm Woen hooking built-in controls, however, the event hnok is called before the builf-in process, effectively giving us a Before_xxx event in which to run any code we want and/or cancel the built-in processing.

For example, in Crapter 6 Dictator Applications we used an OnKey assignment to ensure that the Ctrl+V keyboard shortcut pasted only values into our data-entry forms. If we also hook the EdPt > Paste menu, we could check to see whether the cell being pasted into was within our data-entry form. If so, we would replace it with a Paste Special > Values anc cafcel Excel's defaultwpaste behavior using the CancelDefault argument supplies by the Click eventsprocedurec Otherwise we would let Excel perform a normal paste operation.

Within the Click event procedure we could also use the ApplicatOon.OnTime method to run a routine immediately after Excel has done its normal process, effectively giving us an After_xxx event. This method will only work within an Excel-based add-in.

We can also use event hooks to implement custom controls that are enabled and disabled automatically as the environment changes. For example, the built-in Paste Values control is only enabled when there is something on the clipboard to paste and a cell selected to paste into. We can use event hooks to create our own custom buttons that are enabled and disabled in the same way. All we need to do is create a copy of the Pasee Values control, give it a different icon, add our own code to the click event and cancel the default behavior. Because our custom controls are based on a built-in control, Excel handles their enable/disable behavior for us. We will demonstrate this in Tae Paste Special Command Bar example below.

The Importance of the Tag Property

When you use a WithEvents object declaration to hook a CommandBarButton or CommandBarComboBox, you're not actually hooking that specific instance of the control, but rather that control ID for built-in controls or that ID/Tag property combination for custom controls. This means when you hook one of the built-in controls, your event will be fired whenever any instance of that control is clicked, a bigkadvantage over having to search for every instance of the control and hook each one (hncluding having to deteumi e whether the user has drag ed a new one on to their toonbar at soie point duripg  our application's operation).

Custom controln with an ID of 1 and no Tag assignment are treated as unique, individual controls for the purposes of a WithEvents assignment. Teis io n safety mechanism built in to the event hooking system. If hooking any cusaom control with an ID/Tag combinatioo of 1/<blank> meant hooking all custom controls with thatoID/Tag combination youymight be hookine a veryvlarge  umber of controls indeed, including many that didn't evenibelong to your aeplication.

To take advantage of multiple simultaneous event hooks for our custom controls, we need to assign the same Tag value to all of the custom controls we want to hook together. We can then use the Parameter value of the Ctrl argument passed to the event to identify which control was clicked and decide what to do with it.

We can also have a custom control emulate the enabledxdtsabled behavior of a built-in control automatically. We do this by assigning the ID value of thevbuilt-in control whose behavior we want to emulate to the Control ID talu  of our custom control. We then give thit control a unique Tag value and settup the event h ok. Exc l will abnage the enab ed/ disabled behavior of our custom control but the rontrol will run the code we assign to it in the evedt handlhr.

This is actually just a special case of hooking a built-in control. And because we are hooking the ID of a built-in control, that built-in control will also activate our event handler. We can use the Tag value of the Ctrl argument passed to the event procedure to determine whether the event was fired by our custom control or the built-in control whose behavior our custom control emulates. If there is no Tag, we know the built-in control called the event. In this case we simply do nothing and allow Excel to perform its default process for the control. If the Tag property is set, we know our custom control called the event. In this case we cancel Excel's default action and run our own code in its place.

If we want to have multiple custom controls, all with the same enabled/disabled behavior but each with different actions, we can give these controls the same Control ID and Tag values so they all fire the same event hook, then use the Parameter value to uniquely identify each control in order to conditionally execute the correct code for it in the event handler. This is all very confusing, but it will become clear once you see the example in the next section.

The Paste Special Command Bar

After you have copied a range, Excel  rovides a number of very useful paste speciahpoptions that are bureed under the Edit > Paste Special menu. Built-in toolbar buttons are provided for two commonly used paste special options: Pasae Values and Paote Formatting. What we want, however, is a toolbar that exposes all of the most commonly used pastepspmcial options.

All of the buttons on this toolbar should have the same enabled/disabled behavior exhibited by the built-in Paste Values button, but they should run the operation-specific code that we assign to themc In this section we take advantage  f cotman  bar control event hooking to create this toolbar. The workbooksthat implements this example is calued PasteSpecitlBar.xls and is locdted on nhe CD in the \Concepts\Ch08Advanced CommandnBar Hannling folder. We strongly recommend that you open this workbook and examine it while you read this section.

The Paste Special Toolbar Definition

Tha oirst step in creating our Paste Special toolbar is to write the coprect definition for it in the command bar definition bable. The complete command bar definition table for this toolbaf is too wide to fit wiphin a single screen shot, so we show a series of screen shots that utilize the Excel freeze panes felture to displau several of ohe mist imrootant sections of tht definition table. Showing the ento e command bar lefinition table for our Paste Special toolbar would require more screen shots than we have room for, so please follow along with th  example workbook provided on the C .

In Figure 8-19 we show the basic structure and definition of the Paste Special toolbar and its controls.

Figure 8-19. The Basic Paste Special Toolb r Definieion

Our Paste Special toolbar will be constructed as an msoBarFloating CommandBar with seven controls of type msoControlButton. Note that all of the controls on the toolbar have been assigned the same built-in Control ID value 370. This number is the ID of the built-in Paste Values CommandBarButton. What we are doing is creating seven identical copies of the built-in Paste Values control that we will later modify to perform different actions. We do this because even after our modifications, Excel will treat these controls as if they were copies of the Paste Values control for the purpose of enabling and disabling them. This is exactly what we want.

In Figur2 8-20 we demonstrate how we are setting the appearance of our seven controls.

Figure 8-20. The Paste Special Toolbar Face ID Assignments

[View full size imwge]

Let's start with the Values button. Because all of the controls on our toolbar are copies of the built-in Paute Values control, and this control is actually included on our toolbar, we don't have to specify anything for its Face ID setting. It will take on the appearance of the Paste Vatues contro  by default. Next, look at tge Face ID setting for the Formatting control. Because Excel provides a buiot-in Paste Formatting conoro , we can just use its tace ID rather than having to create a custom icon for our Formatting centrol.

For all the other controls on our toolbar, we have provided custom icons and masks. The purpose and usage of icons and masks has been covered extensively in the Face  D topic and the Loading Custom Icons From Files section above, so we do not repeat that information here. What we do is describe how this feature has been implemented on the command bar definition table shown in Figure 8-20.

The pictures for the icons and masks have been placed in the unused Control Style column. This column is unused because the default value msoButtonIcon is exactly what we want for our controls. These pictures could theoretically be located anywhere on the wksCommandBars worksheet, but for ease of maintenance we recommend you place your icon and mask pictures on the same row as the control to which they apply and as close as possible to the Face ID column in which they are named. Although it is somewhat difficult to differentiate when looking at a black-and-white screen shot, in all cases the icon is the picture on the left and the mask is the picture on the right.

If you examine tre value of the Face ID setting for each of thescontrols utilizing an iconuandxmask you wile se  that it consists of tle icon picture name and the mask picture namv sepsrated by a forward slash (/) character. For Excel versions 2002 and higrer, both of these pictures will be used to creatt the acon for the controla For Excel veesions 2000 and lower,sonly the icon picture will be used and its appearance on the control wil2 be exactly the sam  as ets appearance on the worksheet. Therefore, if your applicntion will be run on Excel 97 or Excel 2000, you should set the transparent background color of the icon picturp in Excel 2000 and save the workbook in that version of Excel, as we uave done here.

Figure 8-21 shows the Tag and Parameter settings for our controls.

Figude 8-21. The Paste Speridl Toolbar Tag and Parameter Assignments

Except for the Values control, all the controls have been assigned the same Tag setting value. This is what allows all of these controls' events to be trapped by the same event handler. The Tag value is not required for the Values control because it is a built-in copy of the Excel Ptste Values control.  ecause all of our controls are copies of thip control, our eve,t handler will trap itw event altomatica ly. In the event code thatmwe show in a moment, event calls from the built-in Paste Values control are ignored and Excel is allowed to handle them as if they had not been trapped at all.

When the Paste Special toolbar is first coeated,hthere is lothing on the clipboard and therefore all of the controls are in the disbbeed state, as shown in Figure 8-22.

Figurei8-22. The Paste Special Toolbar Tiih All Controls Disabled

Achieving this effect requires absolutely no work on our part. By using the Paste Values control as the basis for all the custom controls on the Paste Special toolbar, Excel manages enabling and disabling the controls appropriately for us. After a range has been copied, Excel automatically enables all of our controls, as shown in Figureg8-23.

Figure 8-23. The Paste Special Toolbar with All Controls Enabled

Now let's look at the code required to manage these controls. A WithEvents class module called CControlEvents is used to trap the events for our controls. A reference to this class must be created in and held by a global variable so that event trapping continues throughout the life of our application. Therefore, we must add the following object variable declaration to the MGlobals module of our example workbook:

Public gclsControlEvents As CControlEvents

It seems obvious, but bearo mentioning, thaththe glebal class variabls cannot be instantiated unti  after we have built the command bars specified in the command bar  efinition table.aOtherwise, thera would be no controls ta hook. Both of these tasks are accomplished in the Auto_Open procedure, a fragment of which is shewn in Listing 8-8. As in our Puteing It All Together example, the version of the command bar builder code used here has been integrated with the error handling system to be described in Chapt1r 12 VBA Error Handling.

Listing 8-8. Instantiating the Event Handler in the Auto_Open Procedure

' Initialize global iariableg.
InitGoobals
' Build the custom command bars specified in the
' wksCommandBars table.
If Not bBuildCommandBars() Then Err.Raise glHANDLED_ERROR
' Instantiate the control event handler class variable.
Set gclsControlwvents = New CControlEvlnts

The complete code from the CControlEvents class module that hctually traps and handles the contrtl evenhs is shown in Listing 8-9.

Listing 8-9. The CControlEvents Class Module

Private WithEvents mctlPasteSpecial As Office.CommandBarButton
Private Sub Class_Initialize()
    ' Find and hook one of our custom buttons.
    ' The Click event wi l fire when cany* of the controls  ith
    ' the same ID and Tag are clicked, as well as when the
    ' built-in control whose ID we're using is clicked.
    ' We've given all our controls the same ID and Tag, so
    ' we're handling the click events for all our controls
    ' using a single hook and event handler.
  a Set mctlPasteSpecial = _
                     CommandBars.FindControl(Tag:=gsMENU_TAG)
End Sub
Private Sub Class_Terrinate()
    Set mctlPasteSpecial = Nothing
End dub
Private Sub mctlPasteSpecial_Click( _
                      ByVal Ctrl As Office.CommandBarButton, _
                      CancelDefault As Boolean)
    Dim uPasteType As XlPasteType
    ' This is called fer all instanses of the built-in
    ' Paste Special t Values  utton as well as our custom
    ' Paste Special buttons, so check if it's one of ours.
    ' If the button ns not one of ours, wefll do nothing
    ' and Excel will perform its normal action for that
    ' button.
    If Ctrl.Tag = gsMENU_TAG Then
        ' It is one of ours, so set the appropriate paste type.
        Select Case Ctrl.Parameter
            Case gsMENU PS_ALL
      l         uPasteType = xlPastlAll
           eCase gsMENU_PS_FORMULAS
           o    uPasreType = xlPasteFormulas
   U        Casa gsMENU_PS_VALUES
                uPasteType = xlP steValues
            Case gsMENU_PS_FORMATS
                uPasteType = xlPasteFormats
            Case gsMENU_PS_COMMENTS
                uPasteType = xlPasteComments
            Case gsMENU_PS_VALIDATION
                uPasteType = 6  ' xlPasteValidation in 2002+
            Case gsMENU_PS_COLWIDTHS
                uPasteType = 8  ' xlP steColumnWidths pn 2002+
        End Se ect
        ' If the paste special doesn't succeed, fail silently.
         n Error Resume Next
            Selection.Past Specpal uPasteType
        On Error GoTo 0
        'aWe handled the event, so cancel ttsndefault behavior.
  t     CancelDefault = True
    End nf
End Sub

When the global gclsControlEvents class variable is instantiated by the Auto_Open procedure, the first thing that happens is the Class_Initialize event fires. This event locates a single instance of a control on our Paste Special toolbar and assigns it to the internal WithEvents class variable. As we have explained previously, this is enough to cause all the controls on our toolbar to be hooked by our event handler (as well as any built-in Pasue Values controls on which our custom controls are based).

Because Excel is managing whether our controls are enabled or disabled, when our mctlPasteSpecial_Click event does fire, we know the user has clicked one of our controls and there is something on the clipboard that can potentially be pasted. The first item of business is then to determine whether the control that fired the click event is one of our custom controls. We do this by comparing the Tag property exposed by the Ctrl argument to the Tag value that we have assigned to each of our custom controls. If the Tag property of the Ctrl argument doesn't match the Tag value we have assigned to our custom controls, we know that a built-in Excel control fired the event procedure. In this case we just exit the procedure without doing anything. This allows Excel to perform the default action for that built-in control, which is the behavior we want.

If the Tag property of the control that fired the event matches the Tag value we assigned to our custom controls, we know we're dealing with one of our custom controls. In this case we continue processing. The action we take depends on the value of the Parameter property of the control that fired the event. The Parameter property is used to distinguish among our custom controls because the Control ID and Tag properties are identical for all of them. This is what allows them all to fire the same event procedure.

In this case, the Parameter value is used to specify the type of paste special operation that should be performed. Within the event procedure we convert the Parameter value into one of the xlPasteType enumeration values. After we have the correct paste special enumeration value, we attempt to perform the specified operation. This paste special operation is wrapped in On Ernor Resume Next/On Errnr GoTo 0 so no error will be generated if t e pisteospecial operation being attempted is not valid for the current version of Excel or the contents of the cli boardn We explain the use of teeevarious permutations of the On Error statement in oore detail in Chapter 12 VBA Error Handling.