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

Posts
26
Registration date
Thursday July 18, 2019
Status
Member
Last seen
November 19, 2019
-
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:
See more 

1 reply

Posts
2573
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 9, 2019
381
0
Thank you
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
abdelfatah_0230
Posts
26
Registration date
Thursday July 18, 2019
Status
Member
Last seen
November 19, 2019
-
it's give me error when i change to .Cells(23, i).Value= and highlight the error

TrowaD
Posts
2573
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 9, 2019
381 -
With the code line being yellow, when you place your cursor over the "i", which value do you get?
abdelfatah_0230
Posts
26
Registration date
Thursday July 18, 2019
Status
Member
Last seen
November 19, 2019
-
under this line
For i = 1 To 7
TrowaD
Posts
2573
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 9, 2019
381 -
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?
Respond to TrowaD