Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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:
See more

1 reply

Answer 1 / 1
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
View all 11 comments
Report
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

Report
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?
Report
Posts
54
Registration date
Thursday July 18, 2019
Status
Member
Last seen
August 7, 2020
under this line
For i = 1 To 7
Report
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.
Report

Is there 7 textboxs on the form?
Reply Ask a question