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 with multiple tools

0

hello  

i  try   making  macro    to  copy  data   from  userform  to  sheet  with  multiple  textbox  and  combobox      it  take    from  me   more  time   if  i  have  many  combobox   and  textbox  so   i  truly  apprecitae   if  there  is  way  to  make   the  code  is  short  

thanks

Private Sub CommandButton1_Click()
Dim lr As Integer
Dim ws As Worksheet
Set ws = Sheets("invoice")
Range("e5").MergeArea = Me.TextBox1.Value
 Range("e8").MergeArea = Me.TextBox2.Value
Range("a16").Value = Me.TextBox3
Range("a17").Value = Me.TextBox4
Range("a18").Value = Me.TextBox5
Range("a19").Value = Me.TextBox6
Range("E16").Value = Me.TextBox7
Range("E17").Value = Me.TextBox8
Range("E18").Value = Me.TextBox9
Range("E19").Value = Me.TextBox10
Range("F16").Value = Me.TextBox11
Range("F17").Value = Me.TextBox12
Range("F18").Value = Me.TextBox13
Range("F19").Value = Me.TextBox14
Range("G16").Value = Me.TextBox15
Range("G17").Value = Me.TextBox16
Range("G18").Value = Me.TextBox17
Range("G19").Value = Me.TextBox18
Range("B16").Value = Me.ComboBox1
Range("B17").Value = Me.ComboBox2
Range("B18").Value = Me.ComboBox3
Range("B19").Value = Me.ComboBox4
Range("C16").Value = Me.ComboBox5
Range("C17").Value = Me.ComboBox6
Range("C18").Value = Me.ComboBox7
Range("C19").Value = Me.ComboBox8
Range("D16").Value = Me.ComboBox9
Range("D17").Value = Me.ComboBox10
Range("D18").Value = Me.ComboBox11
Range("D19").Value = Me.ComboBox12
TextBox15.Value = Me.TextBox7.Value * Me.TextBox11.Value
TextBox16.Value = Me.TextBox8.Value * Me.TextBox12.Value
TextBox17.Value = Me.TextBox9.Value * Me.TextBox13.Value
TextBox18.Value = Me.TextBox10.Value * Me.TextBox14.Value
End Sub
Answer
Discuss

Answers

0
Selected Answer

Speed

The attached (working) version of your sheet demonstrates the simplified code below.

My idea to simplify your code is to make the control name in the userform to include the destination cell, with the short type separated by an underscore (so <short code>_<destination cell>)- this underscore is vital to the code.

If you add a new control (or change a destination), you need to follow that pattern (and include where you wnat the value to be on Invoice). The mapping between controls and cells is kept unique since VBA Explorer won't let you give two controls the same name.

That means your TextBox2 ("Customer_ID") becomes txt_E8, ComboBox1 becomes cmb_B16 etc. (I've done all that) and the code becomes just this: 

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Ctrl As Control, r As String, d As String

Set ws = Sheets("Invoice")

For Each Ctrl In UserForm1.Controls
 If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then ' ignore Labels
    r = Ctrl.Name
    If InStrRev(r, "_") > 0 Then 'if _ in name, transfer to cell in name
        d = Right(r, Len(r) - InStrRev(r, "_"))
        ws.Range(d).Value = Ctrl.Value
    End If
 End If
Next Ctrl

ws.Range("G5") = Date 'set today's date in invoice

Me.Hide

End Sub

I've added a line to set cell "G5" to today's date in invoice. I removed the subs you had to add item numbers to the user form automatically (they didn't seem to work and it's easier just to type them I to the text boxes!) 

I've also removed the calculation of item totals  from your User form sub - these are now calculated and shown in the form (if there are values under Units and Price) when you move to another control. These totals are shown with two decimal places, normal for most currencies..That's done a sub like this (for each of the Unit and Price controls):

Private Sub txt_E16_AfterUpdate()
If txt_E16 <> "" And txt_F16 <> "" Then
    txt_G16 = Format(txt_E16 * txt_F16, "#,##0.00")
End If
End Sub

Hope this helps. Happy New Year too (in advance)!

Discuss

Discussion

thanks john  , honestly This work indicates the top of professionalism   you've  saved  me   much time   to   write   many  lines  of code   ,that  what   i  look  for 

best regards,
speed
speed (rep: 40) Dec 31, '20 at 10:03 am
Thanks Speed. Hope that approach works well for you.

Happy New Year! 
John_Ru (rep: 6142) Dec 31, '20 at 10:22 am
Add to Discussion


Answer the Question

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