21

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.

Deduplicator
  • 44,692
  • 7
  • 66
  • 118
user51498
  • 434
  • 4
  • 13
  • 23

2 Answers2

32

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 MSForms.TextBox

Public Property Set TextBox(ByVal oTextBox as MSForms.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.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Gary McGill
  • 26,400
  • 25
  • 118
  • 202
  • Code works great. However, is it possible to do this on the same userform without creating a separate class? – Alex Jan 24 '13 at 22:33
  • @Alex: No, because you need an *instance* of the class for each control. Otherwise, the event handler won't know which control the event is for. If only the event handlers had a "sender" parameter like C# event handlers... – Gary McGill Jan 25 '13 at 09:22
  • @GaryMcGill, I know this is some time ago (well a long time ago :)) but I am dynamically adding comboboxes in my sheet. I was wondering if I can apply the same approach to a worksheet rather than a form? [Here is my question](http://stackoverflow.com/questions/40340345/assign-code-to-change-method-of-a-combobox) – Zac Nov 08 '16 at 08:46
  • 1
    @Zac: yes, it should work exactly the same way. Just put the code "in" the Worksheet module (named Sheet1 etc. by default), and use the appropriate Sheet_xxx event. This will be OK if you have a small, fixed number of worksheets to deal with; it won't really work in a situation where you're dealing with worksheets created by the user (for that you'd probably need to use an add-in). – Gary McGill Nov 08 '16 at 09:20
  • 3
    The code works great for textboxes. When trying to make it work for checkboxes, `CheckBox` needs to be specified as `MSForms.CheckBox`. Thought this information might come in handy for someone. – Marcel May 09 '17 at 12:37
  • I am getting an "Object does not source automation events" on the runtime compile of `Private WithEvents m_oTextBox As TextBox`. Anyone have any ideas? - I know that 4/12 years is a long time ago at this point...(using Office 2016) – casewolf Sep 24 '21 at 20:47
  • Ah, need to change it to: `Private WithEvents m_oTextBox As MSForms.TextBox` and it should compile – casewolf Sep 24 '21 at 20:53
2

In that case you have few options, because event handlers cannot be shared in VBA/VB6

Option 1: Use a central handling function which is 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 your controls in control arrays.

Sub TextBox_ChangeEvent(Index As Integer)
  CommonChangeEvent
End Sub

Sub OtherControlType_ChangeEvent(Index As Integer)
  CommonChangeEvent
End Sub

Combining both options your total event handler count will shrink considerably and the remaining handlers are just brainless stubs for the one true event handler.

Daniel Rikowski
  • 71,375
  • 57
  • 251
  • 329