Putting Textboxes into array for formatting

Question

New to VBA, im trying to put specific TextBoxes into a array and then for that array have conditional formatting where as you type numbers in (In this case, Money) it will apply a comma. so instead of 7000, it will type as 7,000. Any help?

Private Sub FormatTextBoxes()
    Dim i As Variant
    Dim iArray As Variant
    Dim txtBox As Object
    
    iArray = Array(2, 7, 50, 51, 55, 62, 63, 64, 67, 69, 71, 78, 86, 91, 92, 94, 95, 102, 103, 107, 108, 111)
    For Each i In iArray
        On Error Resume Next ' Add error handling
        Set txtBox = Me.Controls("TextBox" & i)
        
        If Not txtBox Is Nothing Then ' Check if the textbox exists
            If IsNumeric(txtBox.Text) And txtBox.Text <> "" Then
                txtBox.Text = Format(Abs(CDbl(txtBox.Text)), "#,###")
            End If
        End If
        On Error GoTo 0 ' Reset error handling
    Next i
End Sub

Answer 1

Basic "control array" example:

Add a class module named clsTxtbox:

Option Explicit

Public WithEvents tb As MSForms.TextBox

Private Sub tb_Change()
    If IsNumeric(tb.Text) And tb.Text <> "" Then
        tb.Text = Format(Abs(CDbl(tb.Text)), "#,###")
        Debug.Print tb.Text
    End If
End Sub

In your userform:

Dim colTB As Collection 'for storing the event-handling objects

Private Sub UserForm_Activate()
    SetupTextBoxes  'initialize event capture
End Sub

Private Sub SetupTextBoxes()
    Dim i As Variant
    Dim iArray As Variant
    Dim txtBox As Object
    
    Set colTB = New Collection
    
    iArray = Array(2, 7, 50, 51, 55, 62, 63, 64, 67, 69, 71, 78, _
                   86, 91, 92, 94, 95, 102, 103, 107, 108, 111)
    For Each i In iArray
        Set txtBox = Nothing
        On Error Resume Next ' Add error handling
        Set txtBox = Me.Controls("TextBox" & i)
        On Error GoTo 0 ' Reset error handling
        
        ' Check if the textbox exists: if Yes then set up Change event
        If Not txtBox Is Nothing Then
            Debug.Print "Found textbox: " & txtBox.Name
            colTB.Add EvtObj(txtBox)  'set up the event capture
        End If
    Next i
End Sub

'create, configure and return an instance of `clsTxtBox`
Function EvtObj(tbox As Object) As clsTxtBox
    Set EvtObj = New clsTxtBox
    Set EvtObj.tb = tbox
End Function