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.
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.
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.
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.
Reply With Quote