1

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?

Millik
  • 21
  • 1
  • 1
  • 4
  • Yes, with a class and WithEvent. See [VBA: Using WithEvents on UserForms](http://stackoverflow.com/questions/1083603/vba-using-withevents-on-userforms) – z̫͋ Jun 25 '14 at 07:55
  • See Control Arrays. From Help. A control array is a group of controls that share the same name and type. They also share the same event procedures. A control array has at least one element and can grow to as many elements as your system resources and memory permit; its size also depends on how much memory and Windows resources each control requires. The maximum index you can use in a control array is 32767. Elements of the same control array have their own property settings. Common uses for control arrays include menu controls and option button groupings. – phd443322 Jun 25 '14 at 08:02

1 Answers1

3

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
z̫͋
  • 1,531
  • 10
  • 15