Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
This chapter is from the book

Trapping Events

A powerful capability built into 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 it will change color to indicate the cell type. When you right-click the cell the color 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\Ch07 – Using 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.

For the purpose of this example we confine ourselves to trapping events associated with a single Worksheet object. You could easily substitute this with a Workbook object if you wanted the code to apply to all the worksheets in a workbook. We need to create a WithEvents object variable in the CCells class module that references the worksheet containing the Cell objects. This 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 we see in a moment.

Figure 7-1

Figure 7-1 The Worksheet event procedures available in CCells

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 the MEntryPoints module to create a new collection with all the links in place to trap the BeforeDoubleClick and BeforeRightClick events for the cells in the worksheet. Double-clicking a cell changes the cell's background to a color that depends on the cell's type. Right-clicking a cell removes the background color.

  • + Share This
  • 🔖 Save To Your Account