VBA: Using WithEvents on UserForms

by

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.

More Related Contents:

  1. Handling Events for OLEObject CommandButtons created at Runtime
  2. When should I use GET or POST method? What’s the difference between them?
  3. JavaScript: remove event listener
  4. How to remove a lambda event handler [duplicate]
  5. CSS selector for a checked radio button’s label
  6. How can I capture the right-click event in JavaScript? [duplicate]
  7. Using the HTML5 “required” attribute for a group of checkboxes?
  8. How to add events to Controls created at runtime in Excel with VBA
  9. Is it bad to not unregister event handlers?
  10. How to prevent robots from automatically filling up a form?
  11. Accessing functions bound to event handlers with jQuery
  12. How to ensure an event is only subscribed to once
  13. How to handle add to list event?
  14. Are event handlers in JavaScript called in order?
  15. Can you require two form fields to match with HTML5?
  16. How to insert text into Outlook email editor using VBA
  17. Uploading file using Google Apps Script using HtmlService
  18. JavaScript add events cross-browser function implementation: use attachEvent/addEventListener vs inline events
  19. Should I always disconnect event handlers in the Dispose method?
  20. What is the right way to POST multipart/form-data using curl?
  21. Can you require two form fields to match with HTML?
  22. within not entirely working, only the last is processed
  23. Exposing .NET events to COM?
  24. Pass custom options to a symfony2 form
  25. How to subscribe to other class’ events in C#?
  26. Delphi Change main form while application is running
  27. Validate fields after user has left a field
  28. Detect when a form has been closed c#
  29. ModX in event.state in tkinter?
  30. Creating a custom event listener on an Android app

Leave a Comment