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: