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 form userform to sheet based on combobox

0

hi

I  try   copying  data  from userform  to  sheet  . first   it  should  match  the  names  in  combobox  with COL B   then   search  the  name   and  copy  data  from  multiple  textboxes  to  rest  of  columns  in sheet . with  considering  the  data  it  will  be  increasing    may  be  reaching  2000 rows  .

this  is  what  I  have   but  doesn't  work. any  assistance  it will be  truly  appreciate .

Private Sub CommandButton1_Click()
Dim ws As Worksheet, Rng As Range, Sel As Variant
  Set ws = Sheets("sheet1")
  Sel = Me.ComboBox1.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
      ws.Cells(Rng.Row , "C") = Me.TextBox1.Value
      ws.Cells(Rng.Row , "D") = Me.TextBox2.Value
      ws.Cells(Rng.Row , "E") = Me.TextBox3.Value
     ws.Cells(Rng.Row , "F") = Me.TextBox4.Value

    End If
  End If
End Sub

Answer
Discuss

Answers

0

Alaa

Your line 

Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)

locates the first instance of whatever is in your ComboBox1 of UserForm1. So for Summir, it would find cell B2.

The code below add the parameter searchdirection:=xlPrevious to that Rng  Find line (so it locates the last instance instead)

To insert a line there (with formatting like the other data rows,) use the code below (changes in bold):

Private Sub CommandButton1_Click()
Dim ws As Worksheet, Rng As Range, Sel As Variant

Set ws = Sheets("sheet1")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
  Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole, searchdirection:=xlPrevious) 'search backawrds!
  If Not Rng Is Nothing Then
      Cells(Rng.Row + 1, "A").Resize(1, 6).Insert Shift:=xlDown, _
       CopyOrigin:=xlFormatFromLeftOrAbove

      ws.Cells(Rng.Row + 1, "A") = Now
      ws.Cells(Rng.Row + 1, "B") = Sel
      ws.Cells(Rng.Row + 1, "C") = Me.TextBox1.Value
      ws.Cells(Rng.Row + 1, "D") = Me.TextBox2.Value
      ws.Cells(Rng.Row + 1, "E") = Me.TextBox3.Value
      ws.Cells(Rng.Row + 1, "F") = Me.TextBox4.Value

  End If
End If

Unload Me
End Sub
Notice that the first full bold line (over two lines) inserts a row below. 

Note: having command buttons with the same name (like CommandButton1) is confusing and should be avoided. You can edit them in the Properties. Normally you'd finish the macro (before End Sub) with

Unload Me
This makes the form disappear and claers the memory it uses. You can add a macro to your button on the worksheet with 
UserForm1.Show

Also, I'd suggest you avoid putting your ComboBox RowSource (your named range list) on another sheet so it can't be altrered by manipulation of data rows (I had to use that Range.Insert method to avoid addding a row to it!

Hope this helps

Discuss

Discussion

Alaa, please note I've just corrected my Answer (in case you already read it)
John_Ru (rep: 6102) Jul 15, '21 at 1:10 pm
John you  acheived the  work   greatly  especailly  when  insert a new  row  automatically  instead  of  insert  manually . but     I    have  a  problem   . it  left    empty rows   and  insert  a  new row  and  fill  data .  it  should  see  to   row which   is empty  from col c: f   if  theses columns  are  empty    it  should  fill  before  insert  any  row  and  if  there  are filled  then  insert row  and  filled   . if  you  don't  understand  me    I  will  attach   file  with  result to  understand  me  more.
Alaa (rep: 28) Jul 15, '21 at 3:40 pm
Alaa
Sorry but I don't really understand what your question is now. Please edit your ORIGINAL question to clarify / attach a revised file. 
John_Ru (rep: 6102) Jul 15, '21 at 4:23 pm
see  my  updating  file   . i hope   helping  to   understand  me 
Alaa (rep: 28) Jul 15, '21 at 4:52 pm
Alaa

I don't understand why you would plan to have empty rows rather than enter a new record (complete with date). It you want them, just work out the logic to check for empty celld (and what to do about a pre-existing date). .It's late here and I don't intend to change or improve my answer, sorry.

For future questions, please note that your original question should be clear. Ant requested clarification should be in the wording of the question, not the file.
John_Ru (rep: 6102) Jul 15, '21 at 5:18 pm
why you would plan to have empty rows rather than enter a new record (complete with date)
because  I  have   another  macro  it  creates  empty rows  for  each  name  ,then  it should  fill  first  after  after  finish  that  and  no  empty rows  for  each  columns  from c: f, then  should  insert a new  row  as  your  updating for the code   . that's  why  I  ask  that. any way  thanks  for  your  time .
Alaa (rep: 28) Jul 17, '21 at 5:52 am
Alaa

Personally, if there's a few names, I'd create "dummy" rows manually (or adjust the macro to add dummy data). Either way delete them once real data starts to populate the sheet. That avoids a macro each time checking for empty rows to populate (long after real data is in the table).

INaturally it's your design so do what you wish.
John_Ru (rep: 6102) Jul 17, '21 at 7:00 am
Add to Discussion


Answer the Question

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