Hi,
As you mentioned in code, you can't write like txtName + i + .Text.
But this is one of the possible way, Create an array with the names of text boxes you want...
Dim ControlNames(1 To 8) As Variant
ControlNames(1) = "TextBox1"
ControlNames(2) = "TextBox2"
ControlNames(3) = "TextBox3"
ControlNames(4) = "TextBox4"
ControlNames(5) = "TextBox5"
ControlNames(6) = "TextBox6"
ControlNames(7) = "TextBox7"
ControlNames(8) = "TextBox8"
after this, you can access as shown below,
For i = 1 To 8
sht.Cells(i, 1).Value = UserForm1.Controls(ControlNames(i)).Text
Next i
Hope this is helpful to you.