Set the value of cells in a row using textboxes on input form

Question

I am trying to create an input form in Excel that sets the values of cells in a row based on a number of Textboxes:

• For each click of command button, update a new row of cells with the current form data.
• Each textbox sets the value of a specific cell in the new row.

I managed to grind out something that works, but it seems clunky. I could end up with n amount of lines (potentially hundreds). It will be a pain to edit or troubleshoot.

I can't figure out how to refer each textbox to the correct cell in each new row.

How can I reduce this code from n number of lines, to a fixed number?

Private Sub CommandButton1_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim n As Long
Dim LastRow As Long

n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
sh.Range("A" & n + 1).Value = Me.TextBox1.Value
sh.Range("B" & n + 1).Value = Me.TextBox2.Value
sh.Range("C" & n + 1).Value = Me.TextBox3.Value
sh.Range("D" & n + 1).Value = Me.TextBox4.Value
sh.Range("E" & n + 1).Value = Me.TextBox5.Value
sh.Range("F" & n + 1).Value = Me.TextBox7.Value
sh.Range("G" & n + 1).Value = Me.TextBox8.Value
sh.Range("H" & n + 1).Value = Me.TextBox9.Value
sh.Range("I" & n + 1).Value = Me.TextBox10.Value
sh.Range("J" & n + 1).Value = Me.TextBox11.Value

End Sub

Answer 1

Maybe something along the lines of looping a pre-defined array:

Private Sub CommandButton1_Click()

Dim sh As Worksheet: Set sh = ThisWorkbook.Sheets("Sheet1")
Dim arr As Variant: arr = Evaluate("=""TextBox""&ROW(1:11)")
Dim lr As Long

With sh
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    For Each el In arr
        .Cells(lr + 1, Replace(el, "TextBox", "") * 1) = Me.Controls(el).Value
    Next
End With

End Sub

---

You could also decide to loop over all UserForm.Controls and check their TypeName property:

Private Sub CommandButton1_Click()

Dim sh As Worksheet: Set sh = ThisWorkbook.Sheets("Sheet1")
Dim ctrl As Control, lr As Long

With sh
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            .Cells(lr + 1, Replace(ctrl.Name, "TextBox", "") * 1) = ctrl.Value
        End If
    Next
End With

End Sub

But with this example you'd go over all controls and that might not be needed.