Create Invoices Using Template with UserForm in Excel

Last time we learnt how to create invoices using VBA with a template. This involved quite a number of variables. Today we discuss how to create invoices using template with userform in Excel.

  • First we study the invoice template carefully and identify the items we wish to use in a user-form
  • Next we copy the items
  • We use the copied items descriptions to give all the labels appropriate captions
  • We also create the same number of text-boxes next to the labels
  • We can also use combo-boxes if found more useful
  • Now we map the data that will be entered in the text-boxes to the cell addresses in the invoice template
  • Once the mapping is done we place a command button on the user-form and code it appropriately so that it transfers the data from the user-from to the invoice template
  • Once the invoice is completed, we save the invoice automatically in ‘.xlsx’ format to a specific folder with a name that consists of customer name, invoice number and date of invoice creation
  • Now we initialize our workbook in such a way that the next invoice number, date and certain other items are automatically filled and many other items are completely cleared to make way for the next invoice creation

The complete VBA code is given below:

  1. VBA code for the command button:Private Sub CommandButton1_Click()
    Range(“B8”) = TextBox1.Text
    Range(“B9”) = TextBox2.Text
    Range(“B10”) = TextBox3.Text
    Range(“B14”) = TextBox4.Text
  2. Range(“D18”) = TextBox5.TextRange(“I9”) = Date
    Range(“I10”) = “Net 30 days”

    Range(“I11”) = TextBox6.Text
    Range(“I12”) = TextBox7.Text
    Range(“I13”) = TextBox8.Text
    Range(“I14”) = TextBox9.Text
    Range(“B21”) = TextBox10.Text
    Range(“C21”) = TextBox11.Text
    Range(“H21”) = TextBox12.Text

    Dim reply As String
    reply = “yes”
    Dim row As Long
    row = 22
    Dim path As String
    path = “C:\invoices\”
    Dim mydate As String
    mydate = Date
    mydate = Format(mydate, “mm_dd_yyyy”)
    Do While reply = “yes” Or reply = “y”
    reply = InputBox(“Do you wish to add another item to the invoice? yes/y for yes and no/n for no.”, “Add more items?”)
    If reply = “no” Or reply = “n” Then
    Application.DisplayAlerts = False

    ThisWorkbook.SaveAs Filename:=path & Range(“B8”).Text & “-” & Range(“I8”).Value & “-” & mydate, FileFormat:=51

    Application.DisplayAlerts = True
    ActiveWorkbook.Close savechanges:=False

    qty = InputBox(“Please enter the quantity of next item.”, “Enter Quantity”)
    Cells(row, 2) = qty
    Description = InputBox(“Please enter a description for your next item.”, “Description”)
    Cells(row, 3) = Description
    unitprice = InputBox(“Please enter the unit-price for next item.”, “Unit Price”)
    Cells(row, 8) = unitprice

    End If

    row = row + 1

    End Sub

  3. VBA code for ‘ThisWorkBook’:Private Sub Workbook_Open()
    Range(“B8:B10”) = “”
    Range(“I9”) = “”
    Range(“I11:I14”) = “”
    Range(“D18”) = “”
    Range(“B21:B33”) = “”
    Range(“C21:C33”) = “”
    Range(“H21:H33”) = “”

Range(“I8”).Value = Range(“I8”).Value + 1
End Sub

  1. Some extra goodies:Private Sub CommandButton2_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
    If TypeName(ctl) = “TextBox” Then
    ctl.Value = “”
    End If
    Next ctl
    End Sub

The above code is for a command button that will clear the data from the text-boxes if you decide not to transfer the data to the invoice.

Private Sub CommandButton3_Click()

End Sub

Tha above code can be used for a command button which indicates that you don’t wish to enter more data to create new invoices.

The code below ensures that the cursor is in the textbox1  when your user-form opens.

Private Sub UserForm_Initialize()

End Sub

Of course you can do much more through the command buttons:

  • Like perform a print action
  • Transfer data to another workbook with worksheets like ‘CustomerData’, ‘ShipmentDetails’, ‘ProductDetails’, etc.

Watch the video (90 MB) below:



10 thoughts on “Create Invoices Using Template with UserForm in Excel

  1. Hardeep Singh

    Hello Sir,
    Need your help?
    I want to pull all the data from sheet1 to sheet2, using a commandbutton and two textbox in which i want to give the date range (such as 09/01/2014 to 09/25/2014) and will pull the complete information between that range from sheet1 (that is master sheet) to Sheet2 (that where I want the data between that date range).

  2. ankit gupta

    i run a distribution company.I purchase goods directly from company and sell it in to the market.
    i do my sale billing sir i m having problem of inventory management.
    If there is any solution that i enter all purchase detail in a sheet which include item details and quantity.and if i can create a invoice template that take party details and item quantity detail from previous mentioned sheet.also update the quantity column of that sheet.also all invoice should get saved in my drive.

    ankit gupta

    1. Efren

      Good Morning Sir Dinesh Kumar Takyar,

      I have a question regarding Invoice Template, How it can be sent the data encoded from User Form Template to a Worksheet. Or to make the Question Clear… I have my Invoice Template, Then I Have my UserForm Prepared and Coded to send it to the Invoice Template, now, How can I send the Data Encoded in the Userform to my Invoice Worksheet Simultaneously? I hope you will answer my question sir. Thank you very much

  3. Mahmoud Essayem

    Dear Dr. Takyar,

    i have created an invoice bineded with a Database for my clients.
    the following columns are to be completed as following:
    B15: B30 = check-in C15: C30 = check-out, D15: D30 = calculated automatically,
    E15: E30 = host name, F15: F30 = number of articles, G15: G30 = item list in the combo box, H15: H30 = tax rates (depending on product selection), I15: I30 = gross price / product, J15: J30 = tax amount, K15: K30 = net amount, L15: L30 = total amount. Everything is okay except an error message:
    in the fields but which are formed are vacant appear following message:
    (#VALUE!) Errors in value.
    Please can you help

    My best regards

  4. Nitin Shahane

    Hello Sir,

    I want to create command button in excel itself say “Send Email”, which will send sheet as pdf to recipients email addresses, mentioned in the same sheet.

  5. Richard B. Alkire

    What invoice template did you use for this. Your VBA code is great but where do i find that invoice template? I want to use it.

  6. Hercules

    Excellent tutorial! I have created 2 templates, 1 for Excel for Mac & 1 for Windows. Mac uses a specific way to send your saved invoices to a specific folder. It took me a while until I got it right. My question is, how can I create a universal template so I can use either or since the template resides on my Network server to be used on whichever machine I’m at? I hope this question was clear enough. I can provide the modified code for each if necessary. Thank you in advance.

  7. joseph

    i have created an invoice using your tutorial.I want my invoice to be saved in the name i want and i want the name to be entered in user form .can you help me thanks

  8. Pingback: Training Form Template – Minecraft

Leave a Reply

Your email address will not be published. Required fields are marked *