Handling Worksheet control events using a class module.

Pages in this article

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