Calling multiple Sub's with one line

Question

I'm trying to call multiple Sub's with one line, at this moment I'm doing this (which's not practical)

' Textbox1
' Textbox2
' Textbox3
' Textbox...
' Textbox100

Private Sub TextBox1_AfterUpdate()

    Call TextBox1_Function

End Sub

Private Sub TextBox2_AfterUpdate()

    Call TextBox2_Function

End Sub

Private Sub TextBox3_AfterUpdate()

    Call TextBox3_Function

End Sub

' ......................... till Textbox100

I wonder now if I could something like this:

' Textbox1
' Textbox2
' Textbox3
' Textbox...
' Textbox100

Private Sub TextBox1_AfterUpdate()

    ' 1 - Get this Sub Object (I don't know if it's possible)
    ' 2 - Then Call the function 
    Call TextBox_Function OBJ_Name

End Sub

Function TextBox_Function(OBJ as Object)

    ' Do some code

End Function

Or anything better... With less line of code... Because I've 10 categories of text, each category have 100 textboxes each...

It've to be different functions for some text boxes, because of that I can't use "For Each Textbox....."

Answer 1

You can add event handlers for multiple text boxes by adding a class that handles the events. This class can include a BoxType so you can define for each TextBox which type it is, and in the event handler you can then distinguish the boxes by their BoxType.

1. Add a Class Module name it clsTextBox and paste the following code:

   Option Explicit
   
   Public WithEvents pTbx As MSForms.TextBox
   Public BoxType As String
   
   Private Sub pTbx_AfterUpdate()
       Select Case BoxType
           Case "TypeA"
               Debug.Print "Type A update " & pTbx.Name
           Case "TypeB"
               Debug.Print "Type B update " & pTbx.Name
       End Select
   End Sub
   
   Private Sub pTbx_Change()
       Select Case BoxType
           Case "TypeA"
               Debug.Print "Type A change " & pTbx.Name
           Case "TypeB"
               Debug.Print "Type B change " & pTbx.Name
       End Select
   End Sub
   

   Create the events you need.

   • pTbx refers to the text box that triggered the event.
   • BoxType specifies the type of the text box that triggered the event.

2. Initialize your text boxes. Therefore paste the following code in your user form

   Option Explicit
   
   Private mClsTbx() As clsTextBox
   
   Private Sub InitBoxes(ByVal BoxType As String, ByVal BoxNames As Variant)
       Dim StartIdx As Long
   
       On Error Resume Next
       StartIdx = UBound(mClsTbx) + 1
       On Error GoTo 0
       ReDim Preserve mClsTbx(StartIdx + UBound(BoxNames))
   
       Dim i As Long
       For i = 0 To UBound(BoxNames)
           Set mClsTbx(i + StartIdx) = New clsTextBox
           Set mClsTbx(i + StartIdx).pTbx = Controls(BoxNames(i))
           mClsTbx(i + StartIdx).BoxType = BoxType
       Next i
   End Sub
   
   
   Private Sub UserForm_Initialize()
       'here we define which TextBox belongs to which type
       InitBoxes BoxType:="TypeA", BoxNames:=Array("TextBoxA1", "TextBoxA2")
       InitBoxes BoxType:="TypeB", BoxNames:=Array("TextBoxB1", "TextBoxB2")
       'add more types here
   End Sub