Dynamically add data into Rows and column using userform VBA

Question

I newbie in VBA-Excel. I have problem to how insert data as dynamic from userform into sheets or table, output what I want is when commandbutton in userform on click then value insert as dynamically based name of participant. here my code:

Private Sub CommandButton1_Click()
Dim BarisSel As Long
Sheets("db").Activate
lRow = Application.WorksheetFunction.CountA(Range("A:A")) + 2

Cells(lRow, 1) = cb_class.Text
Cells(lRow, 2) = cb_room.Text
Cells(lRow, 3) = tb_name.Text

'insert caption for Training Filed and every Question
Cells(lRow, 4) = trainingField.Caption
Cells(lRow, 5) = Qustion_1.Caption
Cells(lRow, 5) = Qustion_2.Caption
Cells(lRow, 5) = Qustion_3.Caption
Cells(lRow, 5) = Qustion_4.Caption

'Answer Question number 1 using OptionButton
If Jwb_1_A Then Cells(lRow, 6) = "A"
If Jwb_1_B Then Cells(lRow, 6) = "B"
If Jwb_1_C Then Cells(lRow, 6) = "C"
'Remarks for Answer B or C using text box
If Jwb_1_B.Value = True Then Cells(lRow, 7) = Tb_1_B.Text
If Jwb_1_C.Value = True Then Cells(lRow, 7) = Tb_1_C.Text


'Answer Question number 2 using OptionButton
If Jwb_2_A Then Cells(lRow, 6) = "A"
If Jwb_2_B Then Cells(lRow, 6) = "B"
If Jwb_2_C Then Cells(lRow, 6) = "C"
'Remarks for Answer B or C using text box
If Jwb_2_B.Value = True Then Cells(lRow, 7) = Tb_2_B.Text
If Jwb_2_C.Value = True Then Cells(lRow, 7) = Tb_2_C.Text


'Answer Question number 3 using OptionButton
If Jwb_3_A Then Cells(lRow, 6) = "A"
If Jwb_3_B Then Cells(lRow, 6) = "B"
If Jwb_3_C Then Cells(lRow, 6) = "C"
'Remarks for Answer B or C using text box
If Jwb_3_B.Value = True Then Cells(lRow, 7) = Tb_3_B.Text
If Jwb_3_C.Value = True Then Cells(lRow, 7) = Tb_3_C.Text


'Answer Question number 4 using OptionButton
If Jwb_4_A Then Cells(lRow, 6) = "A"
If Jwb_4_B Then Cells(lRow, 6) = "B"
If Jwb_4_C Then Cells(lRow, 6) = "C"
'Remarks for Answer B or C using text box
If Jwb_4_B.Value = True Then Cells(lRow, 7) = Tb_4_B.Text
If Jwb_4_C.Value = True Then Cells(lRow, 7) = Tb_4_C.Text

....
....
....
....
....

'Until Question end
End Sub

The output is not what i want, is just overwrite when i change next name participant. This is screenshot in excel for output what I want:

Output I would like:

Answer 1

Your code puts everything in one row. You must add 1 to lRow every time you want to insert something in a new row, e.g.:

'insert caption for Training Field and every Question
Range(Cells(lRow, 4), Cells(lRow + 3, 4)) = trainingField.Caption
Cells(lRow, 5) = Qustion_1.Caption
Cells(lRow + 1, 5) = Qustion_2.Caption
Cells(lRow + 2, 5) = Qustion_3.Caption
Cells(lRow + 3, 5) = Qustion_4.Caption

'Answer Question number 1 using OptionButton
If Jwb_1_A Then Cells(lRow, 6) = "A"
If Jwb_1_B Then Cells(lRow, 6) = "B"
If Jwb_1_C Then Cells(lRow, 6) = "C"
'Remarks for Answer B or C using text box
If Jwb_1_B.Value = True Then Cells(lRow, 7) = Tb_1_B.Text
If Jwb_1_C.Value = True Then Cells(lRow, 7) = Tb_1_C.Text


'Answer Question number 2 using OptionButton
If Jwb_2_A Then Cells(lRow + 1, 6) = "A"
If Jwb_2_B Then Cells(lRow + 1, 6) = "B"
If Jwb_2_C Then Cells(lRow + 1, 6) = "C"
'Remarks for Answer B or C using text box
If Jwb_2_B.Value = True Then Cells(lRow + 1, 7) = Tb_2_B.Text
If Jwb_2_C.Value = True Then Cells(lRow + 1, 7) = Tb_2_C.Text
...

Answer 2

Have you tried using breakpoints to find where your code deviates from the expected behaviour?

If you are unfamiliar; you can set a breakpoint by clicking in the margin next to your code.

Execution will halt when the breakpoint is reached.

You can then execute your code one line at a time, by pressing F8. This is great way to debug VBA, as you can see what each line is doing. While debugging you can hover over variables, and a tooltip will show you the current value.

If you resize the VBA and Excel windows, so both are visible, you will be able to see the output as it is generated. I suspect you will find the code that updates lRow is not functioning as you had expected.