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

Trapping Events

A powerful capability built in to class modules is the ability to respond to events. We want to extend our Analysis application so that when you double-click a cell that has been analyzed, all the cells of the same type will be highlighted. When you right-click the cell, the highlight will be removed. We also want to ensure that cells are reanalyzed when they are changed so that our corresponding Cell objects are kept up to date. The code shown in this section is contained in the Analysis4.xls workbook in the \Concepts\Ch07Using Class Modules to Create Objects folder on the CD that accompanies this book.

To trap the events associated with an object you need to do two things:

Declare a WithEvents variable of the correct object type in a class module.

Assign an object reference to the variable.

The events we want to trap are associated with the Worksheet object. Therefore, we need to create a WithEvents object variable in the CCells class module that references the worksheet containing the Cell objects. The appropriate WithEvents variable declaration is made at the module-level within the CCells class and looks like the following:

Private WithEvents mwksWorkSheet As Excel.Worksheet

As soon as you add this variable declaration to the CCells class module, you can select the WithEvents variable name from the drop-down menu at the top left of the module and use the drop-down menu at the top right of the module to see the events that can be trapped, as shown in Figure 7-1. Event names listed in bold are currently being trapped within the class, as you will see in a moment.

Figure 7-1. The Worksheet Event Procedures Available in CCells

[View full size image]

Selecting an event from the drop-down creates a shell for the event procedure in the module. You need to add the procedures shown in Listing 7-11 to the CCells class module. They include a new property named Worksheet that refers to the Worksheet object containing the Cell objects held by the collection, as well as the code for the BeforeDoubleClick, BeforeRightClick and Change events.

Listing 7-11. Additions to the CCells Class Module

Property Set Worksheet(wks As Excel.Worksheet)
Set mwksWorkSheet = wks
End Property
Private Sub mwksWorkSheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, _
mwksWorkSheet.UsedRange) Is Nothing Then
Highlight mcolCells(Target.Address).CellType
Cancel = True
End If
End Sub
Private Sub mwksWorkSheet_BeforeRightClick( _
ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, _
mwksWorkSheet.UsedRange) Is Nothing Then
UnHighlight mcolCells(Target.Address).CellType
Cancel = True
End If
End Sub
Private Sub mwksWorkSheet_Change(ByVal Target As Range)
Dim rngCell As Range
If Not Application.Intersect(Target, _
mwksWorkSheet.UsedRange) Is Nothing Then
For Each rngCell In Target.Cells
mcolCells(rngCell.Address).Analyze
Next rngCell
End If
End Sub

The CreateCellsCollection procedure in the MEntryPoints module needs to be changed as shown in Listing 7-12. The new code assigns a reference to the active worksheet to the Worksheet property of the Cells object so the worksheet's events can be trapped.

Listing 7-12. The Updated CreateCellsCollection Procedure in the MEntryPoints Module

Public Sub CreateCellsCollection()
Dim clsCell As CCell
Dim rngCell As Range
Set gclsCells = New CCells
Set gclsCells.Worksheet = ActiveSheet
For Each rngCell In ActiveSheet.UsedRange
gclsCells.Add rngCell
Next rngCell
End Sub

You can now execute the CreateCellsCollection procedure in MEntryPoints to create a new collection with all the links in place to trap the double-click and right-click events for the cells in the worksheet. Double-clicking a cell changes the cell's background of all similar cells to a color that depends on the cell's type. Right-clicking a cell removes the background color.