Looping through a 2D array of text boxes

Question

I have a user form having a 100 text boxes. They are arranged 10 x 10 arrays. The text boxes have names

C1_A1  to C1_A10 (first row)
C2_A1  to C2_A10 (second row)
..... 
C10_A1  to C10_A10 (tenth row)

How can I loop through the textboxes row by row. The code for the textbox_change() is given below. I successfully implemented this for a form containing 10 boxes. But now I have to scale to a form having 100 boxes and it is no longer practical.

Private Sub C1_A1_Change()
    Dim wt As Double 
    C1_A1.SetFocus
    If IsNumeric(C1_A1.Value) Then
        wt = CDbl(C1_A1.Value)
        If wt >= 0 And wt <= 1 Then
            'do nothing
        Else
            MsgBox "Enter a number between 0 and 1"
            C1_A1.Value = vbNullString
        End If 
    Else
        wt = 0 
    End If
End Sub


'an action button to read all values 
Private Sub ReadDataTT1_Click()
    Me.C1_A1.Value = Range("Wt!E9").Value
    ............
    Me.C10_A10.Value = Range("Wt!N18").Value
End Sub 


'an action button to save all values  
Private Sub SaveDataTT1_Click()      
    If C1A1.Value <> "" Then
        Range("Wt!E9").Value = C1_A1.Value
        ............  
        Range("Wt!N18").Value = C10_A10.Value
    End If  
End Sub

Answer 1

To use only one single event handler (TextBox_Change Event) for all text boxes you can use a class module.

1. Add a class module called clsTextBox with the following content:

   Option Explicit
   
   Public WithEvents pTbx As MSForms.TextBox
   
   Private Sub pTbx_Change()
       Dim wt As Double
   
       If IsNumeric(pTbx.Value) Then
           wt = CDbl(pTbx.Value)
           If wt >= 0 And wt <= 1 Then
               'do nothing
           Else
               MsgBox "Enter a number between 0 and 1"
               pTbx.Value = vbNullString
           End If
       Else
           wt = 0
       End If
   End Sub
   

   Note that this is the code you used in your TextBox_Change event which we want to apply to all text boxes.

2. Add the following to your user form to apply the class to your text boxes

   Option Explicit
   
   Private mArrClsTbx(1 To 9) As clsTextBox   'change 9 to number of textboxes
   
   Const TbxRows As Long = 3   'change 3 to number of text box rows
   Const TbxCols As Long = 3   'change 3 to number of text box columns
   
   Private Sub UserForm_Initialize()
       Dim i As Long
   
       Dim iRow As Long, iCol As Long
       For iRow = 1 To TbxRows
           For iCol = 1 To TbxCols
               i = i + 1
               Set mArrClsTbx(i) = New clsTextBox
               Set mArrClsTbx(i).pTbx = Controls("C" & iRow & "_A" & iCol)
           Next iCol
       Next iRow
   End Sub
   

3. To read/save the values you can use a loop similar to the one above writing/reading cells instead of the two Set lines.

   Public Sub WriteDataToWorksheet()
       Dim iRow As Long, iCol As Long
       For iRow = 1 To TbxRows
           For iCol = 1 To TbxCols
               Worksheets("Wt").Range("E9").Offset(iRow - 1, iCol - 1).Value = Controls("C" & iRow & "_A" & iCol).Value
           Next iCol
       Next iRow
   End Sub
   
   Public Sub ReadDataFromWorksheet()
       Dim iRow As Long, iCol As Long
       For iRow = 1 To TbxRows
           For iCol = 1 To TbxCols
               Controls("C" & iRow & "_A" & iCol).Value = Worksheets("Wt").Range("E9").Offset(iRow - 1, iCol - 1).Value
           Next iCol
       Next iRow
   End Sub