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
   Else

   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

   Loop
   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
invoice.Show
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()
invoice.Hide

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()
TextBox1.SetFocus

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: