This Stack Overflow answer solves the problem: How can a form receive events from multiple instances of a WithEvents class? The simple answer is to use RaiseEvent but the problem is that when you create a collection of WithEvents classes, the RaiseEvent only works for the last class instance added to the collection. This post shows you how to connect each WithEvents control to a RaiseEvent using a delegate class and receive the event in the form. For example, you could have changes to any one of a group of textboxes trigger a change in another form control.
(When I came across the Stack Overflow post above I read the explanation but not the code. I let it bounce around my brain for a couple of days and then got the main point that you can just funnel the WithEvents control to the delegate when the WithEvents control fires. After that, writing the code was easy.)
Review: Create a Textbox WithEvents Class
In past posts I’ve discussed UserForm code that creates a collection of WithEvents classes to capture changes to all the textboxes:
Public collControls As Collection
Private Sub UserForm_Activate()
Dim ctl As MSForms.Control
Set cDelegate = New clsDelegate
Set collControls = New Collection
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.TextBox Then
Set cEvents = New clsEvents
Set cEvents.cDelegate = cDelegate
Set cEvents.txtBox = ctl
collControls.Add cEvents
End If
Next ctl
End Sub
clsEvents looks like this:
Private Sub txtBox_Change()
debug.print "Textbox changed"
End Sub
That’s nice for creating a message, or opening a file, or some other action outside the form. But sometimes you want to receive the event in the form and use it as a trigger to change other form controls.
You can add an Event to clsEvents so that it can be raised in the txtBox_Change event and received by another class, such as a form, but again the problem is that only the last textbox added to collControls will raise the event. Previously, I’ve coded around that by passing a reference to the userform into clsEvents, and manipulated the form from the class. It works, but it’s dirty. Plus I think it creates a memory leak.
Modification to allow multiple RaiseEvents
The solution is to create another class, a delegate, that is also instantiated in the userform. The delegate has a single public control property that is set by any of the clsEvents instances, one at a time, as their txtBox_Change event fires. When the txtBox variable is passed to the cDelegate it raises its txtBox_Changed event, which is then received by the cDelegate_Change routine back in the form. This way there’s only one instance of cDelegate, but its txtBox variable refers to whichever clsEvents textbox has changed most recently.
Here’s the code for clsDelegate:
Public Sub PassControl(txtBox As MSForms.TextBox)
RaiseEvent txtBoxChanged(txtBox)
End Sub
So simple! And here’s the modified code for clsEvents:
Private m_cDelegate As clsDelegate
Public Property Set cDelegate(value As clsDelegate)
Set m_cDelegate = value
End Property
Private Sub txtBox_Change()
m_cDelegate.PassControl txtBox
End Sub
m_cDelegate holds the reference to the cDelegate instance that’s passed to clsEvents, so it can pass its txtBox instance to the delegate class. In the form code below you can see where cDelegate is instantiated and where that instance is passed to each cEvent instance:
Public WithEvents cDelegate As clsDelegate
Public collControls As Collection
Private Sub UserForm_Activate()
Dim ctl As MSForms.Control
Set cDelegate = New clsDelegate
Set collControls = New Collection
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.TextBox Then
Set cEvents = New clsEvents
Set cEvents.cDelegate = cDelegate
Set cEvents.txtBox = ctl
collControls.Add cEvents
End If
Next ctl
End Sub
Private Sub cDelegate_txtBoxChanged(txtBox As MSForms.TextBox)
Me.Label1.Caption = txtBox.Name & " text length is " & Len(txtBox.Text) & "." & vbCrLf & "Text is: " & vbCrLf & txtBox.Text
End Sub
The last sub in the code is the receiving event. It just updates information about whatever textbox was changed last.
Download!
Here’s a sample workbook with the form and code. Let me know what you think!
Absolutely fantastic! Thanks a lot for sharing. I do always wonder why the RaiseEvent procedure in most custom object events doesnt have any code in it – which effectively makes it a quasi interface object. There is another example put up by Chip Pearson where a RaiseEvent procedure within a public sub has the arguments for the RaiseEvent in the same procedure. Here is the link http://www.cpearson.com/excel/Events.aspx i just couldnt get my head around the utility and relevance of designing it in this manner. Your thoughts would be most appreciated.
Thanks
Mangesh
Thanks Mangesh!
In answer to your question about Chip Pearson’s example and the fact that he seems to be passing the event’s arguments to itself, it just looks that way. Here’s the beginning of the procedure that calls the event:
Sub AAA()
Dim B As Boolean
Dim IDNumber As Long
IDNumber = 1234
Cancel = False
RaiseEvent EventName(IDNumber, Cancel)
He’s actually passing the two variables from the procedure to the event. The procedure variables have the same names as the event arguments, i.e., IDNumber and Cancel. At least, that’s what I think is supposed to be happening. It’s confusing because he declares B as Boolean and it’s not used and he doesn’t declare Cancel.
If I’m understanding correctly and the code was more explicit it would look like this:
This is something I do too. In many ways it makes it very clear what you are passing, but maybe not always! I’ve thought about posting on this to get other people’s takes on it.
Right! See that’s the thing with newbs. Most code online seems to have confusingly similar variables used across a module. But regardless, chips treatment raises a larger question. If you declare fixed arguments in the event procedure which are being passed to the event, how does it allow for different sink classes to specify their own treatment of the event? Would you not say that custom events pass back the control to the calling class to execute a set of actions unique to that class when the event is triggered – sort of an interface object without the implementation aspect. In chips example I don’t know what trying to elucidate (maybe it’s just me – but it certainly seems legit to comment on the approach). Unless you can point me in the correct direction which modifies some impressions above. Cheers Doug
Also, in the modified code with the clsDelegate class module, what purpose does the collection of controls serve? Its declared, instantiated but doesnt seem to be used at all once the controls are added at run time.
The collection lets you add numerous controls to the class dynamically.
Doug,
Thank for this and all of your contributions!
I am trying to connect a few dots between several of your posts and one StackOverflow to see if there are any advantages to implement a UserForm that leverages a Custom Collection (3rd link below) cUserformControlsCollection in-conjunction with a Interface Class IUserFormControl to capture WithEvents for multiple events for multiple types of controls as prescribed in this post and building on your post (1st link below).
In terms of developing something that is dynamic/robust, I assume the Custom Collections Class would need to initialize multiple collections to handle the various control/event types passed through the interface class and then have your class clsMultipleControls (1st link) Implements IUserFormControl .
Maybe inspiration for a new post? In all seriousness, thank you, and any help would be most appreciated.
1) UserForm Event Class – Multiple Control Typeshttps://yoursumbuddy.com/userform-event-class-multiple-control-types/
2) RaiseEvent for Multiple UserForm Controlshttps://yoursumbuddy.com/raiseevent-multiple-userform-controls/
3) Interfaces/Implements in Excel VBAhttps://stackoverflow.com/questions/19373081/how-to-use-the-implements-in-excel-vba/19379641#19379641?newreg=205b620191404451b876a3c275e57b7b
Hi Landon,
Sorry I didn’t answer you sooner, and that I don’t have an answer for you now. Hopefully you’ve figured it out! I rarely come here any more, in fact I just noticed I’ve been down for four days.
Good luck!