Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy data from userform to sheet are not correct in location

0

Hello 

I try copying  data  from  userform to  sheet INV_PUR . so  when  select  combobox "CODE" will fill  the  others  comboboxes  and  fill the  item by  serial number and write  the  values  manually in textbox(QTY), textbox(PRICE)  will calculation in textbox TOTAL   and  when  move  to next row  on  user  form to  select another  item by select combobox  (CODE) will do  the  same  thing , after  filling on userform should  copy  to  sheet INV_PUR  from row 19 . my  problem ignores   the  numbers   in textbox (ITEM) on userform  ,should  copy  to  column B in sheet INV_PUR from row 19  instead  of  that , will copy starting  combobox(CODE)  and  clear words  TOTAL,SUBTOTAL ,TAX   when  try  copy  data to sheet .

so I  put  the  expected result  after filling  data on userform in last   sheet  how  should  be .

Private Sub CommandButton5_Click()
  With Sheets("INV_PUR")
 For i = 1 To 11

         .Cells(i + 18, 2).Value = Me.Controls("TextBox" & i)
      Next i

      For i = 12 To 44
        .Range("F19").Offset((i - 12) Mod 11, Int((i - 12) / 11)).Value = Me.Controls("Textbox" & i)
      Next i

      For i = 2 To 45
''
         .Range("B19").Offset((i - 2) Mod 11, Int((i - 2) / 11)).Value = Me.Controls("ComboBox" & i)
      Next i
   End With
End Sub
Answer
Discuss

Answers

0
Selected Answer

Hi Mussa

Your code has multiple loops but I suggest that you use a single loop for the transfer from the form and also:

  1. add data only when a Userform row is populated (I added a test to see if anything is in the Item textbox)
  2. use a row counter (n) to control that 
  3. insert new rows if tere are too many items (and it would start to overwrite Subtotal rows etc).

You'll need to complete the code but here's what is in the attached revised file:

Private Sub CommandButton5_Click()

  With Sheets("INV_PUR")
    'set row counter
    n = 19
     For i = 1 To 11
        Itm = Me.Controls("TextBox" & i)

        If Itm <> "" Then
          ' add Item
          .Cells(n, 2).Value = Itm
          ' add Code
          .Cells(n, 3).Value = Me.Controls("ComboBox" & i + 1)
          ' add Qty
          .Cells(n, 6).Value = Me.Controls("TextBox" & i + 11)
          ' etc.

          ' increment row counter and add new rows if needed
          n = n + 1
          If n >= 24 Then .Rows(n).Insert
        End If

    Next i

   End With
End Sub

Hope this helps.

Discuss

Discussion

Hi John ,
thanks  for  this  idea . works  for  me  greatly !
just  question  about  what  I  added  to  make  sure  every  thing  is ok , is that right?
 .Cells(n, 3).Value = Me.Controls("ComboBox" & i + 1)
          .Cells(n, 4).Value = Me.Controls("ComboBox" & i + 12)
          .Cells(n, 5).Value = Me.Controls("ComboBox" & i + 23)
          .Cells(n, 6).Value = Me.Controls("ComboBox" & i + 34)
          ' add Qty
          .Cells(n, 7).Value = Me.Controls("TextBox" & i + 11)
          .Cells(n, 8).Value = Me.Controls("TextBox" & i + 22)
          .Cells(n, 9).Value = Me.Controls("TextBox" & i + 33)
Mussa (rep: 48) Oct 18, '22 at 4:50 am
Great, thanks for selecting my Answer, Mussa.

Your additional code works well for me but it is your responsibility to check that the numbering of ALL controls matches that code. Note that I tried entries in Textbox1 (and its row) and TextBox5 (I think). For the latter, the Total was not calculated.

Good luck!
John_Ru (rep: 6142) Oct 18, '22 at 7:15 am
For the latter, the Total was not calculated.
yes  you're right  , not  all of  textboxes will   calculate in attached  file . just  I  gave you sample  up  to  textbox4   to  understand  my  problem ,after  your  solution  I  will complete the  others  procedure  for  the  others  textboxes to  calculation.
Your additional code works well for me
thanks  for  confermation  and  telling  me .
best  regards,
Mussa
Mussa (rep: 48) Oct 18, '22 at 7:43 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login