VBA: Using WithEvents on UserForms

Question

I have a Word userform with 60+ controls of varying types. I would like to evaluate the form every time a control_change event is triggered and change the enabled state of the form's submit button. However, I really don't want to write and maintain 60 on change event handlers.

Answer 1

You can create an event-sink class that will contain the event-handling code for all of your controls of a particular type.

For 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 you need to create & hook up an instance of that class for each control of the appropriate 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 need to add the event handler instances to a collection is simply to ensure that they remain referenced and thus don't get discarded by the garbage collector before you're finished with them.

Clearly this technique can be extended to deal with other types of control. You could either have separate event handler classes for each type, or you could use a single class that has a member variable (and associated property & event handler) for each of the control types you need to handle.