excel - Implementing a change event to check for changes to textbox values and enabling the "apply" button

Question

I need your help,

Is it possible in Excel VBA to apply some sort of a global change event handler for a specified userform for all textboxes and comboboxes. I know it would be possible to do them 1 by 1 but if you have around 20 textboxes this would be overkill for writing code _change for each of the userform controls no?

I'd essentially like to enable an apply button after the user has typed in any textboxes or selected any new values from a combo box and change a button enabled property from false to true.

I scoured the web but couldn't find a solution that matches my requirements.

Any help is greatly appreciated. I am sorry that I am going over my head on my VBA level of knowledge here.

Answer 1

Is it possible in Excel VBA to apply some sort of a global change event handler for a specified userform for all textboxes and comboboxes.

Because of how event handlers are wired to event sources in VBA, the answer is "no".

However...

Add a new class module to your project, call it DynamicTextBox (you could have another for a DynamicComboBox).

Option Explicit
Private WithEvents encapsulated As MSForms.TextBox

Public Sub Initialize(ByVal ctrl As MSForms.TextBox)
    Set encapsulated = ctrl
End Sub

Private Sub encapsulated_Change()
    'handle textbox changed here
End Sub

Now in your form, have a Collection of DynamicTextBox class instances - you'll want to have a New instance for each TextBox you want to handle the Change event for.

Option Explicit
Private handlers As VBA.Collection

Private Sub UserForm_Initialize()
    Set handlers = New VBA.Collection
    Dim ctrl As MSForms.Control
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.TextBox Then
            Dim textBoxHandler As DynamicTextBox
            Set textBoxHandler = New DynamicTextBox
            textBoxHandler.Initialize ctrl
            handlers.Add textBoxHandler
        'ElseIf TypeOf ctrl Is MSForms.ComboBox Then
        '    Dim comboBoxHandler As DynamicComboBox
        '    Set comboBoxHandler = New DynamicComboBox
        '    comboBoxHandler.Initialize ctrl
        '    handlers.Add comboBoxHandler
        End If
    Next
End Sub

Note, this technique of encapsulating a WithEvents MSForms control in a class instance, can also be used for handling events of controls that are added dynamically at run-time.

The key is to keep the MSForms controls in scope - that's what the module-level Collection does.