[ACCEPTED]-VBA: Using WithEvents on UserForms-ms-word

Accepted answer
Score: 32

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.

Score: 2

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()
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)
End Sub

Sub OtherControlType_ChangeEvent(Index As Integer)
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