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

Report
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021
-
 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
2695
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
457
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
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021

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

Posts
2695
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
457
With the code line being yellow, when you place your cursor over the "i", which value do you get?
Posts
69
Registration date
Thursday July 18, 2019
Status
Member
Last seen
January 15, 2021

under this line
For i = 1 To 7
Posts
2695
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
457
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?

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!