2

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.

0m3r
  • 12,286
  • 15
  • 35
  • 71
Jason Kelly
  • 2,539
  • 10
  • 43
  • 80
  • 1
    The closest you'll come will be to write one procedure that looks for whatever conditions you need to test for, then call it from each `*_change` event handler. _Hint: Pass in the control you're testing._ You're better off doing it this way, anyway - if your "OK to save" conditions ever change in the future, you only have one place to make the changes instead of 20. **NOTE:** since there is no broken code in this question, it'll probably be closed as OT, but I do at least want to point you in the right direction. – FreeMan Nov 02 '18 at 17:24
  • You could try using excel to build your code into a string, then copy paste that into the vba editor – KySoto Nov 02 '18 at 18:13

2 Answers2

3

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.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Thank you for this Mathieu. While the textbox portions work great. Why would I get a runtime error 13, type mismatch on Public handlers As VBA.Collection – Jason Kelly Nov 02 '18 at 18:43
  • The collection has no business being public, and given declarations aren't executable, I doubt you're getting a *run-time* error on a non-executable statement. – Mathieu Guindon Nov 02 '18 at 18:44
  • I see it now. I had to add Option Explicit Private WithEvents encapsulated As MSForms.ComboBox Public Sub Initialize(ByVal ctrl As MSForms.ComboBox) Set encapsulated = ctrl End Sub Private Sub encapsulated_Change() 'handle textbox changed here MsgBox ("changed") End Sub – Jason Kelly Nov 02 '18 at 18:47
1

This is a very subjective question, so there isn't one right answer. I can think of a trick to do it, but depending upon what your application does, it could be an overkill. Outlining the steps below.

  1. Loop through all the controls on the form, and store their text values in an array. Something like

    For Each ctl In frm.Controls
 'Save the values in an array 
Next ctl

  2. Use an event, such as click on the form. Something else could also work based on what your form does.

  3. At the click event, check each control's value with the array for a change. If yes, enable the 'Apply' button and update the array

Running the above at each click could be too much process load, and may slow your application. So think of any alternative if your form allows.

shash
  • 246
  • 1
  • 7