VBA: Detect changes in any textbox of the userform

There is a userform that has many textboxes and I need to detect changes in each. So I have write a subroutine for every textbox in the form and it turns out a large piece of code. As the code for every textbox is the same I want to optimize it. So is it possible to write just one subroutine that detect changes in any textbox of the form?


  • The only way do achieve that is to use a class along with WithEvents

    Here's a minimal example:

    Code for the class module named mytextbox:

    Private WithEvents txtbox As MSForms.TextBox
    Public Property Set TextBox(ByVal t As MSForms.TextBox)
        Set txtbox = t
    End Property
    Private Sub txtbox_Change()
        ' code for handling the event
    End Sub

    And the code inside the Userform, assuming you want to handle the events of every Textbox

    Private myEventHandlers As Collection
    Private Sub UserForm_Initialize()
        Dim txtbox As mytextbox
        Set myEventHandlers = New Collection
        Dim c As Control
        For Each c In Me.Controls
            If TypeName(c) = "TextBox" Then
                Set txtbox = New mytextbox
                Set txtbox.TextBox = c
                myEventHandlers.Add txtbox
            End If
        Next c
    End Sub