- Creating Objects
- Creating a Collection
- Trapping Events
- Raising Events
- Practical Example
- Summary
This chapter is from the book
This chapter is from the book
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 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.