[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()
  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.

Source: stackoverflow.com

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

Best way to get the name of a button that called an event?

Form.Load event not firing, form showing

NullReferenceException when triggering event

JTable: Detect cell data change

jQuery event on value change of input hidden

JCheckbox change listener gets notified of mouse over events

Why use event listeners over function calls?

Which methods can be used to make thread wait for an event and then continue its execution?

Passing arguments to an event handler

Do you need to remove an event handler in the destructor?