Using Invoice Template to Automate Invoice Creation and Printing with VBA

Somebody rightly said: All cash flow starts with an invoice. We can use an invoice template to automate the invoice creation and printing with VBA.

  • First we download an invoice of our choice right from within MS Excel when we start the program or software
  • Next we save it as a normal file ‘.xlsx’ file
  • Now we study the various elements of the invoice which we can fill out automatically using VBA
  • We copy the elements headings and paste them in our Excel worksheet
  • Next we enter the data as we make the sales
  • We place a command button on our worksheet by clicking on the developer tab, selecting the insert option and from the ActiveX controls we click on the ‘command button’ icon. We can now click and drag to draw a command button at a place of our choice on the Excel worksheet. We right-click on the command button, select properties and from the new window that pops up we select the text box next to the item caption and insert our text, for example, print invoices
  • We double click on the command button in the deign-mode and between the two lines of code that are shown in the Visual Basic for Applications Editor (VBE) we start writing our own code
  • First we define the different variables that we will need
  • Now we get the data from the worksheet cells into our variables
  • We now assign the variables to the relevant worksheet cells in the invoice file
  • Since the variables already have values from the worksheet stored in them, these values are assigned to the invoice worksheet cells.
  • We have mapped a few interesting items. The others you can do as an exercise to firm up your understanding of VBA
  • Once the data is entered into the invoice worksheet cells automatically we save the file in a specific manner using the path to the directory, the invoice number, customername, etc
  • Then we make the file read only
  • Also we place automatically a text value called ‘done’ under the header in our Excel data worksheet
  • This ensures that these files will not be saved and printed again by our program or macro

The complete VBA code is given below:

Private Sub CommandButton1_Click()

‘First we define a few variables to capture data from our Excel worksheet
Dim customername As String
Dim customeraddress As String
Dim invoicenumber As Long
Dim r As Long
Dim mydate As String
Dim path As String
Dim myfilename As String

‘our last row of data in the worksheet is defined
lastrow = Sheets(“CustomerDetails”).Range(“A” & Rows.Count).End(xlUp).row

‘ we start at row 2 since the first row contains headers
r = 2

‘Looping process starts
For r = 2 To lastrow

‘If the value under the header ‘Note’ is done then the data in the row is not processed and jump to the label nextrow

If Cells(r, 17).Value = “done” Then GoTo nextrow

‘we map excel worksheet data to the variables

customername = Sheets(“CustomerDetails”).Cells(r, 1).Value
customeraddress = Sheets(“CustomerDetails”).Cells(r, 2).Value
invoicenumber = Sheets(“CustomerDetails”).Cells(r, 6).Value
quantity = Sheets(“CustomerDetails”).Cells(r, 18).Value
Description = Sheets(“CustomerDetails”).Cells(r, 19).Value
UnitPrice = Sheets(“CustomerDetails”).Cells(r, 20).Value
SalesTaxRate = Sheets(“CustomerDetails”).Cells(r, 16).Value

Cells(r, 17).Value = “done”
Application.DisplayAlerts = False
Workbooks.Open (“C:\invoices\BasicInvoice.xlsx”)

‘ now we map the variables to the invoice worksheet data
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“I8”).Value = invoicenumber
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“C8”).Value = customername
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“C9”).Value = customeraddress
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“B21”).Value = quantity
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“C21”).Value = Description
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“H21”).Value = UnitPrice
ActiveWorkbook.Sheets(“BasicInvoice”).Range(“D18”).Value = SalesTaxRate

path = “C:\invoices\”
mydate = Date
mydate = Format(mydate, “mm_dd_yyyy”)

‘File is saved as a read-only file
ActiveWorkbook.SaveAs Filename:=path & invoicenumber & “-” & customername & “-” & mydate & “.xlsx”
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True
ActiveWorkbook.PrintOut copies:=1

‘We close the active workbook. We don’t need to save it because we just saved it!
ActiveWorkbook.Close SaveChanges:=False

‘our label next row. Labels have a colon after their name


Next r

End Sub

View the training video. If you are not able to view the data conveniently then use the full-screen mode.


Watch the video on YouTube.