Creating an Event Handler Class

We've all seen cases where, in a userform, we need multiple controls of the same type to call essentially the same procedure, with some small changes. This is easily achieved by writing a parameter driven procedure and calling it for the appropriate event for each instance of the control type in question.

i.e. something like this
VB Code:
Private Sub OptionButton1_Click()
    MyOptionClicker "Option1"
End Sub
But what do we do when we need to have multiple event procedures for each of a particular type of control and/or we have a large number of controls of that type on the form?

The answer is to use a class module as an Event Handler for the type of control in question.

By using a class module as an Event Handler, we will only need to write each event procedure once, within the class, and we will not need to add any event handlers, for that control type, to the forms code page.

This approach basically involves 3 steps.

Create a Class Module that contains a reference, with events, to the control type in question.
Add the required event handlers to the class module.
Within the _initialize event of the form, create an instance of the event handler for each instance of the control type, and associate that instance of the event handler with that instance of the control type.

Once these 3 steps are completed, each instance of the control type in question will have a 'hook' into the event handler class and will call the class event handlers whenever needed.

A Simple Example:

The brief is to create a form with 6 OptionButtons.
When the mouse is moved over an OptionButton, it should turn Bold and Blue (unless it selected - see next), and revert to "unbold" and black when the mouse moves off it.
When an OptionButton is selected, it should turn Bold and Red, and should remain as such until another OptionButton is selected.

(OK, its a really trivial example, but should get the point across.)

We could add a separate _MouseMove and _Change event for each of the 6 OptionButtons, but we're going to use a class with a single _MouseMove and _Change event that will work for all the option buttons.

Step1/ Create a Class Module that contains a reference, with events, to the control type in question.
We add a class module, which I've called "cOptBtnEventHandler" and add a reference, with events, to the OptionButton object. We also add a read/write property to the class to access this reference.
VB Code:
' **************************************************************
' Class-level Declarations Follow
' **************************************************************
 
Private WithEvents oOption As msforms.OptionButton
 
' **************************************************************
' Class Properties Follow
' **************************************************************
 
Property Get OptionButton() As msforms.OptionButton
    Set OptionButton = oOption
End Property
 
Property Set OptionButton(OptButton As msforms.OptionButton)
    Set oOption = OptButton
End Property
Step 2/ Add the required event handlers to the class module.
We need to handle 2 events for our OptionButtons.
The _Mousemove event, to trap when the mouse is over the OptionButton, turning the OptionButton Bold and Blue.
The _Change event, to turn the OptionButton Bold and Red when it is selected, and back to "unbold" and black when it is deselected.

We add both these event handlers to the "cOptBtnEventHandler" class module.
VB Code:
' **************************************************************
' Class Event Procedures Follow
' **************************************************************
 
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: When the option button is selected, Bold the font
'           and change the text color to Red.
'           When deselected, "unbold" the font and change the
'           text colour back to Black.
'
' Date          Developer       Action
' --------------------------------------------------------------
' 21 Jun 06     Declan Kenny    Initial Version
'
Private Sub oOption_Change()
    
    With oOption
        
        If .Value Then
            oOption.Font.Bold = True
            oOption.ForeColor = &HFF& 'Red
        Else
            oOption.Font.Bold = False
            oOption.ForeColor = &H0& 'Black
        End If
    End With
 
End Sub
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: When the mouse moves over a non-selected
'           OptionButton, change the font to bold and the text
'           color to Blue.
'
' Date          Developer       Action
' --------------------------------------------------------------
' 21 Jun 06     Declan Kenny    Initial Version
'
Private Sub oOption_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With oOption
        If .Value = False Then
            oOption.Font.Bold = True
            oOption.ForeColor = &HFF0000 'Blue
        End If
    End With
End Sub
Step 3/ Within the _initilize event of the form, create an instance of the event handler for each instance of the control type, and associate that instance of the event handler with that instance of the control type.
Before we write the _initialize event, we need to declare a modular level array of our Event Handler class.
VB Code:
'Declare an array of our event handler class
Private acMyOpt() As cOptBtnEventHandler
Now in the _initialize event, we associate an instance of the event handler with each OptionButton.
VB Code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: When the form is initialized, add an event handler
'           for each OptionButton
'
' Date          Developer       Action
' --------------------------------------------------------------
' 21 Jun 06     Declan Kenny    Initial Version
'
Private Sub UserForm_Initialize()
Dim oMyControl As Control
 
    'Set the initial Scope of the array
    ReDim acMyOpt(0)
    
    For Each oMyControl In Me.Controls
        
        'For all OptionButtons..
        If TypeName(oMyControl) = "OptionButton" Then
            
            '..Add a new instance of the Event Handler Class
            Set acMyOpt(UBound(acMyOpt)) = New cOptBtnEventHandler
            
            '..And point that instance of the class to the current
            'OptionButton
            Set acMyOpt(UBound(acMyOpt)).OptionButton = oMyControl
            
            'Increase the array by one element
            ReDim Preserve acMyOpt(UBound(acMyOpt) + 1)
            
        End If
    Next oMyControl
    
    'select the first OptionButton
    'by default
    With Me.OptionButton1
        .Value = True
        .SetFocus
    End With
End Sub
And that's pretty much it. Now whenever the _MouseMove or _Change event of an OptionButton is called, the Event Handler's corresponding event will be called. I.e. no need to write separate _event procedures for each OptionButton.

We do need to add one more procedure to the form. When the mouse is moved off an OptionButton, we need to reset its font. we achieve this by using the Forms _MouseMove event to reset all OptionButtons, except the selected one.
VB Code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: When the mouse moves off a control, reset all
'           OptionButtons to "unbold" and black, except for the
'           selected OptionButton
'
' Date          Developer       Action
' --------------------------------------------------------------
' 21 Jun 06     Declan Kenny    Initial Version
'
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim oControl As Control
    
    For Each oControl In Me.Controls
        
        If TypeName(oControl) = "OptionButton" Then
            
            If oControl.Value = False Then
                
                oControl.Font.Bold = False
                oControl.ForeColor = &H0&
                
            End If
        End If
    Next oControl
 
End Sub
The attached spreadsheet has the complete code in this example and will show you the event handler in action.