Adjusting code transfer data from combo box & textbox into userform to sheet

Report
Posts
54
Registration date
Thursday July 18, 2019
Status
Member
Last seen
August 7, 2020
-
 Facebook -
Hello,



i would help adjusting this code it doesn't work it gives me error when i transfer data to sheet
i want to begins transfer data from a23:g23 of course automatically when i fill a new data insert a new row under a23:g23 with the same format
Private Sub CommandButton1_Click()
Dim My_sh As Worksheet
Set My_sh = Worksheets("sheet1")
Dim Lastrow As Integer
Dim i% Application.ScreenUpdating = False
With My_sh Lastrow = .Cells(Rows.Count, 1).End(3).Row + 1
.Range(.Cells(Lastrow - 1, 1), .Cells(Lastrow - 1, 7)).Copy
.Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteFormats
For i = 1 To 7
.Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i)
Me.Controls("TextBox" & "combobox" & i) = ""
Next
End With
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "ok"
end sub


this code gives me the error





and this is my userform :


and this is my sheet1:

1 reply

Posts
2647
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 3, 2020
434
Hi Abdel,

You are getting an error because you have TextBox# and ComboBox#, but not a TextBoxComboBox#.

You could change:
.Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i)
Me.Controls("TextBox" & "combobox" & i) = ""

into:
.Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & " " & Me.Controls("ComboBox" & i)
Me.Controls("TextBox" & i) = vbnullstring
Me.Controls("ComboBox" & i) = vbnullstring

Best regards,
Trowa
Posts
54
Registration date
Thursday July 18, 2019
Status
Member
Last seen
August 7, 2020

it's give me error when i change to .Cells(23, i).Value= and highlight the error

Posts
2647
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 3, 2020
434
With the code line being yellow, when you place your cursor over the "i", which value do you get?
Posts
54
Registration date
Thursday July 18, 2019
Status
Member
Last seen
August 7, 2020

under this line
For i = 1 To 7
Posts
2647
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 3, 2020
434
Yes, under that line, when the code line is yellow. So when you get the error, click on Debug and then place your cursor over the "i". Doesn't matter which of the three "i" 's.
Is there 7 textboxs on the form?