VBA Code to Copy UserForm Textbox data to specific Excel sheet/cells

[Scuba]

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

[arnelgp]

range("A1:B18") is not 3 x 6? it is 18 rows by 2 columns

[Scuba]

Hello Arnelgp,

Yes I'm aware of that, thank you, as per my original message I did state:

'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'

I was trying to get it to work first the easier way before I then tried to replicate the 3 x 6 format, once I got it copy to correctly to A1:B18 and can then sort out the correct formatting, once step at a time for me as I'm still learning and was hoping for some help from this forum, unfortunately your message isn't offering any help

[georgiboy]

The below and attached may help:

Private Sub CommandButton1_Click()
    Dim nCol As Integer
    Dim nRow As Integer
    Dim myArray() As Variant
    Dim i As Integer
    Dim j As Integer
    
    nCol = 3
    nRow = 6
    
    ReDim myArray(1 To nCol, 1 To nRow)
    
    For i = 1 To nCol
        For j = 1 To nRow
            myArray(i, j) = Me.Controls("TextBox" & ((i - 1) * nRow + j)).Value
        Next j
    Next i
    
    Range("A1").Resize(nRow, nCol) = Application.Transpose(myArray)
End Sub

[Scuba]

Many thanks georgiboy,

Smashed it out of the park!

Once again, thank you.

Regards

Richard