VBA - Populating grid from UserForm

[Gemini774]

Dear all,

I am trying from a userform containing BOTH comboboxes and textboxes to populate a grid line by line.
I am able to populate the grid based on both the comboboxes and the textboxes, however, the 2 textboxes always fill in the 2 first columns of the grid, instead of simply filling in the corresponding columns of the grid.
The textboxes are the 2 last fields of the userform.
I succeeded in moving to the right cells that the textboxes should be populating by using the Offset function.
In the iNextRow dimension I tried replacing "Row" by "Column" but it does not affect anything.

Below you can find below the code I am using (I am filling in 8 combboxes and then 2 textboxes in th esame userform):

Private Sub CommandButton1_Click()

    Dim iComboBox As Long
    Dim iNextRow As Long
    Dim iTextBox As Long

    With Worksheets("Contact")
        ' find next row to populate. Contact is the sheet to be populated as of row 5 which is blank at the beginning.
        iNextRow = .Cells(.Rows.Count, 4).End(xlUp).Row + 1
        For iComboBox = 1 To 8
        .Cells(iNextRow, iComboBox).Value = Me.Controls("ComboBox" & iComboBox).Text
        Next

 iNextRow = .Cells(.Rows.Count, 4).End(xlUp).Row
 ActiveCell.Select
 Selection.Offset(0, 10).Select
          
        For iTextBox = 1 To 2
        .Cells(iNextRow, iTextBox).Value = Me.Controls("TextBox" & iTextBox).Text
        Next
End With

' Now clearing and hiding the userform:

Unload Me
Createprofiles.Hide
 
End Sub

Thanks a lot for your kind help.

 

[Fluff]

Should the combos be going into columns A:H & the textboxes into I:J?

 

[Gemini774]

Should the combos be going into columns A:H & the textboxes into I:J?

Yes exactly!

 

[Fluff]

Ok, how about

Private Sub CommandButton1_Click()

    Dim iComboBox As Long
    Dim iNextRow As Long
    Dim iTextBox As Long

    With Worksheets("Contact")
        ' find next row to populate. Contact is the sheet to be populated as of row 5 which is blank at the beginning.
        iNextRow = .Cells(.Rows.Count, 4).End(xlUp).Row + 1
        For iComboBox = 1 To 8
            .Cells(iNextRow, iComboBox).Value = Me.Controls("ComboBox" & iComboBox).Text
        Next
        For iTextBox = 1 To 2
            .Cells(iNextRow, iTextBox + 8).Value = Me.Controls("TextBox" & iTextBox).Text
        Next
    End With

' Now clearing and hiding the userform:

Unload Me
Createprofiles.Hide
 
End Sub

 

[Gemini774]

Ok, how about

Private Sub CommandButton1_Click()

    Dim iComboBox As Long
    Dim iNextRow As Long
    Dim iTextBox As Long

    With Worksheets("Contact")
        ' find next row to populate. Contact is the sheet to be populated as of row 5 which is blank at the beginning.
        iNextRow = .Cells(.Rows.Count, 4).End(xlUp).Row + 1
        For iComboBox = 1 To 8
            .Cells(iNextRow, iComboBox).Value = Me.Controls("ComboBox" & iComboBox).Text
        Next
        For iTextBox = 1 To 2
            .Cells(iNextRow, iTextBox + 8).Value = Me.Controls("TextBox" & iTextBox).Text
        Next
    End With

' Now clearing and hiding the userform:

Unload Me
Createprofiles.Hide
 
End Sub

it is working I am just struggling to understand why you wrote Textbox + 8 thanks a lot

 

[Fluff]

You're iTextBox counter starts at 1, so to get the data into col 9 you need to add 8 to it.

 

[Gemini774]

You're iTextBox counter starts at 1, so to get the data into col 9 you need to add 8 to it.

Thank you

 

[Fluff]

Glad to help & thanks for the feedback.