Pages in this article
-
Preparations
-
Class Module
-
Hooking up
-
Conclusion
Hooking up the controls to the class module
Now that the event sub is in place the blueprint code is ready. All
that needs to be done now is to hook up the controls on the worksheet to
the class module.
First we'll write some code in the class module that will receive the
object it is going to "listen" to from an initialisation routine I'll
show later:
Public Property
Set Control(obtNew As
MSForms.OptionButton)
Set mobtOption = obtNew
End Property
Now we need to create just as many instances of this class module as
there are controls to hook up and tie each control to its own instance
of the class module. Insert a normal module in the project.
We'll use a Collection variable to hold the instances of the class
modules:
Dim mcolEvents As
Collection
Then we'll loop through all OLEObjects on the worksheet and hook up
the Optionbutton controls to the event, here is the code for the entire
module:
Option Explicit
Dim mcolEvents As
Collection
Sub InitializeEvents()
Dim obtOptionbutton
As OLEObject
Dim osh As
Worksheet
Dim clsEvents As
clsObtHandler
Set osh = ThisWorkbook.Worksheets(1)
If mcolEvents Is
Nothing Then
Set mcolEvents =
New Collection
End If
'Loop through all the controls
For Each
obtOptionbutton In osh.OLEObjects
If
TypeName(obtOptionbutton.Object) = "OptionButton"
Then
'Create a new instance of the event handler class
Set clsEvents = New
clsObtHandler
'Tell it to handle the events for the text box
Set clsEvents.Control =
obtOptionbutton.Object
'Add the event handler instance to our collection,
'so it stays alive during the life of the workbook
mcolEvents.Add clsEvents
End
If
Next
End Sub
Sub TerminateEvents()
'Here the collection of classes is destroyed
so memory will be freed up:
Set mcolEvents =
Nothing
End Sub
The final step is to make sure the InitializeEvents code gets run at
e.g. Workbook_Open (in the ThisWorkbook module):
Option Explicit
Private Sub
Workbook_Open()
InitializeEvents
End Sub
Shutting down and tidying up
When the workbook is closed, or you want to stop the class from
responding to events, you also need code to clean up when the class is
terminated:
Private Sub
Class_Terminate()
Set mobtOption =
Nothing
End Sub
This ensures the memory that is holding the control object reference
is freed once the class is set to nothing:
(in a normal module)
Sub TerminateEvents()
'Here the collection of classes is destroyed
so memory will be freed up:
Set mcolEvents =
Nothing
End Sub
If you step to the above sub, you will see that the Class_Terminate
subroutine will be run for each instance of the class as has been
created in the subroutine "InitialiseEvents". Of course you want this
sub to run when your workbook closes, so in the ThisWorkbook module,
add:
Private Sub
Workbook_BeforeClose(Cancel
As Boolean)
TerminateEvents
End Sub