[ACCEPTED]-VBA: Using WithEvents on UserForms-ms-word
You can create an event-sink class that 18 will contain the event-handling code for 17 all of your controls of a particular type.
For 16 example, create the a class called
TextBoxEventHandler as follows:
Private WithEvents m_oTextBox as TextBox Public Property Set TextBox(ByVal oTextBox as TextBox) Set m_oTextBox = oTextBox End Property Private Sub m_oTextBox_Change() ' Do something End Sub
Now 15 you need to create & hook up an instance 14 of that class for each control of the appropriate 13 type on your form:
Private m_oCollectionOfEventHandlers As Collection Private Sub UserForm_Initialise() Set m_oCollectionOfEventHandlers = New Collection Dim oControl As Control For Each oControl In Me.Controls If TypeName(oControl) = "TextBox" Then Dim oEventHandler As TextBoxEventHandler Set oEventHandler = New TextBoxEventHandler Set oEventHandler.TextBox = oControl m_oCollectionOfEventHandlers.Add oEventHandler End If Next oControl End Sub
Note that the reason you 12 need to add the event handler instances 11 to a collection is simply to ensure that 10 they remain referenced and thus don't get 9 discarded by the garbage collector before 8 you're finished with them.
Clearly this technique 7 can be extended to deal with other types 6 of control. You could either have separate 5 event handler classes for each type, or 4 you could use a single class that has a 3 member variable (and associated property 2 & event handler) for each of the control 1 types you need to handle.
In that case you have few options, because 8 event handlers cannot be shared in VBA/VB6
Option 1: 7 Use a central handling function which is 6 called from every event handler.
Sub Control1_ChangeEvent() CommonChangeEvent // Just call the common handler, parameters as needed End Sub Sub Control2_ChangeEvent() CommonChangeEvent End Sub ... Sub CommonChangeEvent(/* Add necessary parameters */) //Do the heavy lifting here End Sub
Option 2: Organize 5 your controls in control arrays.
Sub TextBox_ChangeEvent(Index As Integer) CommonChangeEvent End Sub Sub OtherControlType_ChangeEvent(Index As Integer) CommonChangeEvent End Sub
Combining 4 both options your total event handler count 3 will shrink considerably and the remaining 2 handlers are just brainless stubs for the 1 one true event handler.
More Related questions