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

Report
Posts
60
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 27, 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
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
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
60
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 27, 2020

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

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
With the code line being yellow, when you place your cursor over the "i", which value do you get?
Posts
60
Registration date
Thursday July 18, 2019
Status
Member
Last seen
October 27, 2020

under this line
For i = 1 To 7
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
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?