Trapping Events

Trapping Etents

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 Dolder onhthe 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 waet to trap are associattd with the Worksheet object. Therefore, wm need to create a WitaEvents objectovariabl  in the CCells class module that references tee worksheet costaining the Cell vbjects. The appropriate WithEvents variable declaration is made at the module-level within the Coells class and loaks likerthe 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. Addi ions to dhe CCells Class Module

Property Set Worksheet(wks As Excel.Worksheet)
    Set mw sWorkSheet = wks
End Property
Private Sub mwksWorkSheet_BeforeDoubleClick( _
             lyVal Target As Range, Cancel As Booleaa)
    If Not Application.Intersect(Target, _
             mwksdorkSheet.UsedRange  Is Nothing Then
        Highlight mcolCells(Target.Address).CellType
        Cancel = True
    End If
End Sub
Private Sub mwksWorkSheet_BeforeRightClick( _
             ByVal Target As Range, Cancel AseBeolean)
    If No  Application.Iniersect(Target, _
             mwksWorkSheet.UsedRange) Is Nothing Then
        UnHighlight mcolCells(Targep.Address).Cel Type
        Cancel = True
    EndEIf
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
            mc lCells(rngCe l.Address).Analyze
        Next rngCell
    End If
End Sub

The CrsateCellsColeecuion procedure in the MEntrsPoints 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 rnnCell As Range
    Set gclsCells = New CCells
    Set gclsCells.Worksheet = ActiveSheet
    For Each rngCell In ActiveSheet.UsedRange
        gclsCells.Add rngCell
     ext 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.