The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

• Quarterdeck
• What's New
• Microsoft Word Tips
• Consulting
• Chain Locker
• Lucky Bag
• Contact
• Search

Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.

This Microsoft Word Help & Microsoft Word Tips page provides the home or small business Word user with a fully automated, interactive template for creating invoice documents. The template is based on a previous template that I co-developed with Microsoft Word MVP and longtime friend MVP Graham Mayor Interactive Invoice Template, and a VBA method that I have refined to monitor table cell events described here: Table Cell Events.

I want mention and thank Jeff Vandervoort for sharing with me the basic range monitoring technique that I employed in this project, Jason Frost Simpson for his interest in the project and assistance with structured code and Graham Mayor as, is customary, his comments, suggestions and critical eye always results in improvements.

The basic template layout is shown below. It consist of a company information section, customer information section, general invoice information section, and a section for invoice product and service line item entries.

• Content controls (e.g., the "Company name" control selected) are used as placeholders for; 1) Invoice number and date, 2) User defined default values i.e., company name, address, telephone number, fax number, e-mail address, website and slogan, 3) User defined customer information i.e., name, address, ID, order number and e-mail address and 4) User defined invoice information i.e., sales person, project, terms, and due date.
• The invoice number and date are defined automatically by the template when each new invoice document is created.
• Default company information is defined by the user during template setup via a template displayed userform. Company information can be revised at any time in a current invoice document via the content controls or by using the invoice defaults userform.
• Customer information and general invoice information is defined by the user when each new invoice document is created via template displayed userform. Customer information and most general invoice information can be revised in the current invoice document via the content controls or the template displayed userform.
• Invoice line item section allows the user to invoice up to 16 products or services on a single page invoice document. The "Qty., Item #, Price and Totals" cells for each line item entry are monitored by the template and detect user actions. This permits real time analysis of user actions and enhanced template performance.
• The dashed lines shown in the illustrations are table gridlines that appear on screen to define table and cell boundaries. The gridlines are not visible on printed invoices.

Notes:
    1. Mapped content controls are placed in the invoice footers and multi-page headers that display company name, invoice number and date.

    2. "Invoice number and Due date" are a calculated values and cannot be edited directly using their associated content control in the invoice document.

Default company information is defined by the user during setup with the template displayed userform "SET INVOICE DEFAULTS (FIRST USE)" shown below. This form displays automatically on first use to allow the user to set the default values for new invoices. It can be displayed again at any time that an invoice document is open should the user wish to change default data, the archive folder location, or associate\unassociated an external data source with the invoice.

Notes:
    1. Due to the method used in sequencing the invoice number the starting number must be defined in the "Invoice No." field including any desired leading zeros. This field is limited to six characters.

    2. Use the "Leading Text" field to fully define "all" text you want to prefix the numerical invoice number (e.g., in the example below the invoice number will be prefixed with "Invoice No.: GKM-").

    3. "Invoice No., Company Name, Address Lines 1 & 2, Date Format and Default Tax Rate
(A)" are mandatory fields that must be completed before the defaults can be saved and applied.

    4. The "Default Tax Rate (A)" defines the tax rate applied to all invoice lines items when an individual line item tax rate is not explicitly defined in the invoice column "T" using tax rate B, C, or D. This includes line items in which column "T" is left blank. Tax rates "B, C, and D" are optional, but if you invoice products or services that are not taxed then you must define a tax rate "0.00" as shown in the "Tax Rate B" field enter "B" in the ""T" column of the invoice when invoicing those products or services.

    5. The template automatically creates an "Archive Folder" named "Invoices" in your Word documents folder. Users can change the "Archive Folder" location using the "Select Archive Folder" command button

Once the default values are set, the user is ready to create the first invoice. Initial data entry for the invoice is accomplished using the interactive template displayed userform shown below.

Notes:
    1. Customer name and address are mandatory fields.

    2. The template user can enter customer information manually or use the "Pick from Address Book" or "Pick From OUTLOOK" command buttons to retrieve customer data.
        - Both buttons function to retrieve that data that you have stored in your address book or OUTLOOK contacts folder.
        - The "Pick from Address Book" will probably provide a quicker response as it doesn't require starting an instance of OUTLOOK, but the data available for retrieval is limited to name, address and e-mail address.
        - While its use is very limited in the template published here, "Pick from OUTLOOK" provides a method to access "all" OUTLOOK contact properties (e.g., Customer ID, used in this application). Using the "Pick from OUTLOOK" may take a little longer or be noticeably slower for users with a large OUTLOOK contact list.
       - If OUTLOOK is not already running, the presence of 3rd party add-ins may cause OUTLOOK to crash. If you use this method and experience OUTLOOK problems then always start OUTLOOK before preparing invoice documents using this template.

    3. If you use either the address book or OUTLOOK to enter customer information, addressing to customer contacts with an associated "company name" is automatically formatted as follows:

      4. "Due Date" is a calculated value determined by the invoice date and terms selected.

     5. The form can be redisplayed at any time in an open invoice document if the user wishes to make changes.

When the user clicks the "Build\Update Invoice" command button the basic invoice appears and the user can begin creating invoice line item entries.

Notes:
    1. With the exception of "Invoice number, Invoice date and Due date," users can edit invoice data in the current invoice directly using the content controls.

    2. As depicted above, if you don't assign a default value for your company telephone number, fax number, e-mail address or website address during setup the corresponding line\content control is removed from your invoice documents. Here, I have left the "Fax" field blank in my setup and the corresponding "Fax:" field has been removed.

    3. If you later decide that you want to restore a deleted information field in new invoices then you will need to perform the following:

       - Create a new invoice document.
       - Open the "Invoice Defaults" dialog by selecting the "Set Defaults" control in the "Invoice Control" group of the "Invoice Manager" ribbon tab.
       - Enter the appropriate default data and click "OK" to save the revised default data and close the dialog.
       - To preserve your invoice numbering sequence, close the new invoice just created without saving changes.
       - Create a new invoice document and the new defaults will display.

     4. Similarly, if you do not provide information in the "Customer E-mail, Customer ID o r Order Number" fields when you create new invoice documents the corresponding line\content control will be removed from the invoice document. Click "Edit Invoice Data" if you decide later to add the deleted information to the current invoice.

Creating the invoice line item entries takes full advantage of the active cell events described in Table Cell Events.

Valid numerical entries in the QTY. and PRICE cells are indicated with temporary
green shading using the OnEntry and OnChange events.


Line item AMOUNT is automatically calculated using the QTY. and PRICE
OnEntry and OnChange events.


Invalid numerical entries in both QTY. and PRICE cells are indicated
with temporary red shading with the OnChange event.


OnExit event prevents the user from entering and processing invalid entries.


The OnExit event automatically format valid numerical entries in the PRICE cell.


Line item totals are automatically calculated and displayed with the
OnChange event  in the line item QTY. and PRICE cells.
Similarly, invoice Sub-total, Tax and Total is automatically calculated.


The OnEnter event detects entry in a calculated cell and flags the entry
 (expect for final total cell) with temporary yellow shading and
 prevents the user from making direct changes to a calculated cell value.
 

Note: Should the user tab or click in the last cell i.e., the invoice "Total" cell, the OnChange redirects the insertion point to the first line item entry. This prevents the user from accidently using the tab key in the final cell as this would add a new row and corrupt the structure of the calculated table.

Item #, DESCRIPTION and U/I cell entries can be done manually by simply tabbing between fields and entering data or by using an external data source associated with the template. Associating an external data source or "Invoice Item List" simplifies entering frequently invoiced products or services. The "Invoice Item List" is simply a formatted Word document that lists these items and associated details. A sample formatted "Invoice Item List" is shown below.

With an invoice item list is associated and enabled when the user enters the "Item #" cell the defined products and services are displayed in a template displayed userform.

The item selected and quantity entered is placed in the selected row of the invoice when the user clicks the "Insert Selected Item" command button. The line item total and invoice totals are automatically calculated when the entry is made.

To create and associate an "Invoice Item List" with the template use the "Create Item List" command button in the "Invoice List Tools" group of the "Invoice Manager" ribbon tab. This creates a new pre-formatted products and services item list like the example shown above that you can customize for you own use. The new item list is automatically named "Invoice Item List.docx," saved in the default "Archive Folder" and associated with the template.

Notes:
    1. User's with more than one business may create multiple item list but only one item list can be associated with the template at a given time.

    2. Item lists can be associated or disassociated with the template using the "Data Source" section of the template displayed Userform "SET INVOICE DEFAULTS" dialog shown below. Call this dialog using the "Set Defaults" ribbon command button.

The user interface (U/I) associated with the template is shown in the two split images below.

invoicer 18

The U/I is arranged in five functional groups "Invoice Control, Item List Tools, Current Invoice, Symbols & Rates and Template Information." A brief description of each control is provided using ribbon super-tip text when the user pauses the mouse over a control. Additional information is provided below.

• Insert Logo - Opens the users default picture folder and allows the user to select and insert a small image "Logo" in the top left corner of the invoice. Logos can be applied to individual invoices or a default logo can be applied to the template file by opening the template file and executing this function.

• Set Defaults - Opens the "Invoice Defaults" dialog. Users can display the defaults dialog anytime an invoice document is open to change default values or associate/unassociate external data files.
• Re-Start Monitor - Re-starts the "Active Cell Monitor" routine. This is a maintenance\convenience control for advanced users. The typical user will not have need to use this control.
• Stop Monitor - Stops the "Active Cell Monitor" routine. This is a maintenance\convenience control for advanced users. The typical user will not have need to use this control.
• Calculate - Manually executes the template "AutoTallyTable" routine. This is a maintenance\convenience control for advanced users. The typical user will not have need to use this control.
• Create Item List - Builds a new pre-formatted products and services item list that users can associated with their invoice template. The new item list is automatically named "Invoice Item List" and saved in the default "Archive Folder." User's with more than one business may create multiple item list but only one item list can be associated with the template at a given time. Use the "Set Defaults" and "Data Source" section of the "Invoice Defaults" form to associate and unassociated data sources.

Note: Each product and service item list created using the "Create Item List" control is named "Invoice Item List.docx" and saved in the default "Archive Folder." Therefore, before attempting to create multiple data source files the current "Invoice Items List.docx" must be moved, renamed or deleted. Otherwise the template displays the following user notification:

• Edit Item List - As the name implies, the control is used to open and edit the data file that the user has associated with the template.
• Item List Enabled - This is a toggle button control used indicate and control the state of an associated item list.

Note: Since it is possible to associate an item list to an open invoice that could contain revised data, the template notifies the user if the item list was created or saved after the current invoice was last saved.

• Insert Row - Enabled when the selection is in an invoice line item row. Inserts a new row immediately below the row containing the insertion point. If the insertion point is is row 16 (or the last line item entry row) then this function inserts rows to fill the current page and adds a new page to the invoice (35 rows are added in total).
• Delete Row - Enabled when the selection is in an invoice line item row. Deletes the current row and adds a new blank row. This preserves the 16 total line item entry rows of the basic invoice.

Note To minimize risk of corrupting the invoice table structure and calculation process, users should avoid using Word's built-in table row add and delete controls

• Edit Invoice Data - Displays the template Userform used to define customer and general template information.
• Stamp Invoice - Allows user to "stamp" or apply one of five pre-defined or a user defined background watermark to the invoice.

Notes:
    1. Any existing "stamp" is automatically removed when this control is executed.

    2. Any stamp text that exceeds (won't fit) in the invoice line item rows will be masked by the shading applied to the heading and total rows. Therefore you should ensure any stamp text is sized to fit within the white space defined by the line item rows.

• Save Invoice - For new (i.e., never saved invoices), displays Word's built-in "SaveAs" dialog configured by the template with a pre-configured suggested file name and file save as location. For open invoices (saved once), saves the invoices.
• Save As PFD - Automatically creates and saves a copy of a saved invoice in PDF format.
• Print Invoice - Prints the invoice using the default printer.
• E-Mail Invoice - Gets or creates an instance of Microsoft OUTLOOK and creates a new addressed mail item. A PDF format copy of the invoice is applied as an attachment.

Notes:
    1. OUTLOOK must be installed on the user's PC and set as the default mail handler.

    2. Uses the default mail account or the account defined by the user during setup.

• Close Invoice - Closes the invoice using Word's built-in process.
• Currency Symbol - Use the dropdown field to apply one of six alternative currency symbols to the invoice calculated values. User the "Before\After number" toggle button to place the currency symbol before or after calculated values.

Note: The default currency symbol is determined by the users regional settings.

• Tax Rate Edit Boxes - The tax rate edit boxes displayed on the U\I show the current tax rates applied to the invoice. User's can modify the tax rates using these controls. Changes made in these controls are saved as new default tax rate settings.
• Help/Info - Opens the tips page on my website
• Disclaimer - Displays a copy of the "Credits and Disclaimer" form.

Download the template here: Invoice (with Auto Table Calculate).  When you attempt to use the template for the first time to create a new invoice, you will be required to acknowledge the "Credits & Disclaimer" and you will be prompted to define the defaults.

For more on template add-ins and how to load them, see: Organizing Your Macros/Template Add-ins at: Installing Macros

That's it! I hope you have found this tips page useful and informative.

Well that is almost it. I have refrained from making this statement in other tips pages but feel that it is appropriate here. I am not a formally trained, professional programmer or Word developer. While what have demonstrated and provided here may be rudimentary in some circles, it represents over 60 hours of tedious and often frustrating work, testing, and re-work. I was happy to do it because I enjoy the challenge and I am pleased to share it with the Word user community. However, I will not respond to requests to further refine or customize this project without demonstrated willingness from the requester to compensate me for the work.

This page was last updated on March 09, 2021

Website Privacy Statement

Back to top    Back to Word Tips