Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Hooking Command Bar Control Events

In Office 2000, Microsoft added the Click event to the Command BarButton object and the Change event to the CommandBarComboBox object to provide an event-based mechanism for working with command bar controls in addition to the OnAction property. The primary reason for this was to allow controls to be used by non-VBA code, such as COM Add-ins, which were introduced at the same time. There are, however, subtle differences in the behavior of a control when using an event hook compared to using the OnAction property. We can exploit this behavior 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, event hooks behave exactly like OnAction procedures. They run when the control is clicked. When hooking built-in controls, however, the event hook is called before the built-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 Chapter 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 Edit > 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 and cancel Excel's default paste behavior using the CancelDefault argument supplied by the Click event procedure. Otherwise we would let Excel perform a normal paste operation.

Within the Click event procedure we could also use the Application.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 Paste 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 The 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 big advantage over having to search for every instance of the control and hook each one (including having to determine whether the user has dragged a new one on to their toolbar at some point during your application's operation).

Custom controls with an ID of 1 and no Tag assignment are treated as unique, individual controls for the purposes of a WithEvents assignment. This is a safety mechanism built in to the event hooking system. If hooking any custom control with an ID/Tag combination of 1/<blank> meant hooking all custom controls with that ID/Tag combination you might be hooking a very large number of controls indeed, including many that didn't even belong to your application.

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 enabled/disabled behavior of a built-in control automatically. We do this by assigning the ID value of the built-in control whose behavior we want to emulate to the Control ID value of our custom control. We then give that control a unique Tag value and set up the event hook. Excel will manage the enabled/ disabled behavior of our custom control but the control will run the code we assign to it in the event handler.

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 provides a number of very useful paste special options that are buried under the Edit > Paste Special menu. Built-in toolbar buttons are provided for two commonly used paste special options: Paste Values and Paste Formatting. What we want, however, is a toolbar that exposes all of the most commonly used paste special 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 them. In this section we take advantage of command bar control event hooking to create this toolbar. The workbook that implements this example is called PasteSpecialBar.xls and is located on the CD in the \Concepts\Ch08Advanced Command Bar Handling folder. We strongly recommend that you open this workbook and examine it while you read this section.

The Paste Special Toolbar Definition

The first step in creating our Paste Special toolbar is to write the correct definition for it in the command bar definition table. The complete command bar definition table for this toolbar is too wide to fit within a single screen shot, so we show a series of screen shots that utilize the Excel freeze panes feature to display several of the most important sections of the definition table. Showing the entire command bar definition table for our Paste Special toolbar would require more screen shots than we have room for, so please follow along with the example workbook provided on the CD.

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 Toolbar Definition

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 Figure 8-20 we demonstrate how we are setting the appearance of our seven controls.

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

Face ID 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 the value of the Face ID setting for each of the controls utilizing an icon and mask you will see that it consists of the icon picture name and the mask picture name separated by a forward slash (/) character. For Excel versions 2002 and higher, both of these pictures will be used to create the icon for the control. For Excel versions 2000 and lower, only the icon picture will be used and its appearance on the control will be exactly the same as its appearance on the worksheet. Therefore, if your application will be run on Excel 97 or Excel 2000, you should set the transparent background color of the icon picture in Excel 2000 and save the workbook in that version of Excel, as we have done here.

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

Figure 8-21. The Paste Special 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 Paste Values control. Because all of our controls are copies of this control, our event handler will trap its event automatically. In the event code that we 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 created, there is nothing on the clipboard and therefore all of the controls are in the disabled state, as shown in Figure 8-22.

Figure 8-22. The Paste Special Toolbar with 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 Figure 8-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 bears mentioning, that the global class variable cannot be instantiated until after we have built the command bars specified in the command bar definition table. Otherwise, there would be no controls to hook. Both of these tasks are accomplished in the Auto_Open procedure, a fragment of which is shown in Chapter 12 VBA Error Handling.

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

' Initialize global variables.
InitGlobals
' 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 gclsControlEvents = New CControlEvents

The complete code from the CControlEvents class module that actually traps and handles the control events 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 will fire when *any* of the controls with
' 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.
Set mctlPasteSpecial = _
CommandBars.FindControl(Tag:=gsMENU_TAG)
End Sub
Private Sub Class_Terminate()
Set mctlPasteSpecial = Nothing
End Sub
Private Sub mctlPasteSpecial_Click( _
ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
Dim uPasteType As XlPasteType
' This is called for all instances of the built-in
' Paste Special > Values button as well as our custom
' Paste Special buttons, so check if it's one of ours.
' If the button is not one of ours, we'll 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
uPasteType = xlPasteAll
Case gsMENU_PS_FORMULAS
uPasteType = xlPasteFormulas
Case gsMENU_PS_VALUES
uPasteType = xlPasteValues
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  ' xlPasteColumnWidths in 2002+
End Select
' If the paste special doesn't succeed, fail silently.
On Error Resume Next
Selection.PasteSpecial uPasteType
On Error GoTo 0
' We handled the event, so cancel its default behavior.
CancelDefault = True
End If
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 Paste 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 Error Resume Next/On Error GoTo 0 so no error will be generated if the paste special operation being attempted is not valid for the current version of Excel or the contents of the clipboard. We explain the use of the various permutations of the On Error statement in more detail in Chapter 12 VBA Error Handling.