Hello,
I have a user form set up with 18 separate textbox's in a 3 x 6 layout, after the user has input their data into said textboxes, upon clicking the save button, I wish to save the data over to a specific tab within my workbook called 'FormsControl Sheet', ideally in the same 6 x 6 layout, see screen shot below:
I have tried the below code but keep getting an error on
Set textbox = Me.Controls(i)
Yes I know the code below won't copy over in same format 3 x 6, one step at a time for me as I'm new to VBA lol
Can anyone help me please, where am I going wrong?
Private Sub SaveButton_Click()'Declare variables
Dim ws As Worksheet
Dim textbox As Control
Dim i As Integer
Dim rng As Range
'Set the worksheet object
Set ws = ThisWorkbook.Worksheets("FormsControl Sheet")
'Set the range of cells where you want to copy the textboxes
Set rng = ws.Range("A1:B18")
'Loop through all of the textboxes on the userform
For i = 1 To Me.Controls.Count
Set textbox = Me.Controls(i)
'If the control is a textbox, then copy its contents to the Excel sheet
If TypeName(textbox) = "TextBox" Then
rng.Offset(i - 1, 0).Value = textbox.Text
End If
Next i