The Invoice Generator is an Excel template with a range of VBA programs designed to automate the process of generating invoices and provide a centralized overview of invoice status. This tool simplifies the administrative tasks involved in invoice management, reducing the time and effort required for invoice creation.
- Purpose
- Features
- Repository Structure
- Requirements
- How It Works
- Customize VBA Code
- Contributing
- License
The purpose of this tool is to streamline the invoicing procedure and provide a comprehensive view of invoice-related information. It aims to make the invoice creation process efficient, taking only a few clicks and 2-3 minutes per invoice, instead of 20-30 minutes. By consolidating invoice data in one place, it allows users to track completed jobs, manage open invoices, and monitor revenue.
- Easy customer data management: Input and update customer information in the Masterdata tab.
- Simple invoice management and creation: Update invoice information in the Invoice tab and generate invoices in both PDF and Excel formats with a single click.
- Email integration: Generate a draft email with an attached PDF invoice for efficient customer communication.
- Dashboard insights: Utilize the Calculations tab to prepare data for informative charts on the Dashboard tab, allowing you to monitor monthly revenue effectively.
- Customizable template: Personalize the invoicing template by adding your logo, modify the workbook, or edit the VBA code according to your company's specific requirements.
The Invoice Generator repository is structured as follows:
- Invoice_Generator.xlsm: The main Excel template that comes with a range of Macros and Forms. It serves as the core tool for automating the process of invoice generation. Users can customize and adapt the template according to their specific requirements.
- VBA Code: This folder contains the comprehensive range of VBA code that powers the Invoice Generator programs in Invoice_Generator.xlsm.
- Forms: This subfolder houses the code responsible for various Form events.
- Modules: This subfolder contains the code for module sub procedures.
- PDF Invoices: This folder contains the PDF versions of created invoices. These sample invoices showcase the formatting and layout of the generated invoices.
- Excel Invoices: This folder contains the Excel versions of created invoices. These sample invoices showcase the formatting and layout of the generated invoices.
- Sample Email Outputs: This folder includes sample Outlook emails that are generated by the VBA programs. It provides examples of the emails that can be generated with PDF invoice attached.
- README.md: Provides an overview of this repository.
- LICENSE: The license file for the project.
- Microsoft Excel (version 2010 or later) with macros enabled.
- Basic knowledge of VBA and Microsoft Excel is recommended to make modifications to the template.
To get started with the Invoice Generator, follow these steps:
- Update Master Data: Input the original customer master data in the Master Data tab. Add new customers using the Add Customer button on the Dashboard tab. Edit or delete customer information by clicking on the Edit Master Data button.
- Add Invoice Information: When receiving a new invoice, enter the relevant information in the Invoice tab. Start by entering the customer's name in the designated orange cell, then press Enter and choose the appropriate customer from the provided list. Proceed to complete the remaining fields for the invoice.
- Generate Invoices and Email: To generate invoice files for an open invoice, click on the Create Invoices button on the Invoice tab. This will present you with a list of open invoices that have a blank status. Choose the desired open invoice and click on "CREATE INVOICE" button within the form. This will automatically populate the invoice template in Template tab with relevant information, export the updated invoice template as both PDF and Excel files and store them in two separate folders dedicated to each file format. If you want to generate a draft Outlook email to customer with PDF invoice attached, continues to click on "Create Email" button. This action will create and save the draft emails within your Outlook email application.
- Update the Dashboard: Performing the actions above will automatically update the Calculations tab, which houses the data preparation table used to generate the chart showcased on the Dashboard tab.
The VBA Code Glossary below provides an overview of the different components and functionality of the VBA code used in the Invoice Generator programs. It lists the various controls, their related events and sub procedures, along with their descriptions and locations within the Excel workbook. This glossary serves as a handy reference for understanding the VBA code structure and helps users navigate and customize the Invoice Generator according to their specific needs.
| Controls | Control Type | Control Location | Related Userform / Module | Component Type | Related Events / Sub Procedure | Event Type | Description |
|---|---|---|---|---|---|---|---|
| Add Customer | Button | Dashboard sheet | MainInvoiceGenerator | Module | add_data_to_master() | Module Sub Procedure | Open the form FormInputMaster |
| Add Customer | Button | Dashboard sheet | FormInputMaster | Userform | btADD_Click() | Userform Event | Add a new record to or update an existing one in Customer Master Data |
| Add Customer | Button | Dashboard sheet | FormInputMaster | Userform | btClear_Click() | Userform Event | Clear all input fields |
| Add Customer | Button | Dashboard sheet | FormInputMaster | Userform | btCancel_Click() | Userform Event | Exit and Close the form |
| Edit Master Data | Button | Dashboard sheet | MainInvoiceGenerator | Module | edit_view_master_data() | Module Sub Procedure | Open the form FormViewMaster |
| Edit Master Data | Button | Dashboard sheet | FormViewMaster | Userform | btEdit_Click() | Userform Event | Edit specific customer's information in the FormInputMaster |
| Edit Master Data | Button | Dashboard sheet | FormViewMaster | Userform | btDelete_Click() | Userform Event | Delete specific customer's information from the Master Data |
| Edit Master Data | Button | Dashboard sheet | FormViewMaster | Userform | btCancel_Click() | Userform Event | Exit and Close the form |
| Search Box | Orange Cell | Invoice sheet | shInvoice | Worksheet | Worksheet_Change() | Worksheet Event | Trigger FormSearch to appear if a value is entered into the designated orange cell |
| Search Box | Orange Cell | Invoice sheet | FormSearch | Userform | tbCustomer_Change() | Userform Event | List search results if matched customer name is found |
| Search Box | Orange Cell | Invoice sheet | FormSearch | Userform | tbCompany_Change() | Userform Event | List search results if matched company name is found |
| Search Box | Orange Cell | Invoice sheet | FormSearch | Userform | btSelect_Click() | Userform Event | Insert the selected customer into the Invoice table |
| Search Box | Orange Cell | Invoice sheet | FormSearch | Userform | btClear_Click() | Userform Event | Clear search entries |
| Search Box | Orange Cell | Invoice sheet | FormSearch | Userform | btCancel_Click() | Userform Event | Exit and Close the form |
| Create Invoices | Button | Invoice sheet | MainInvoiceGenerator | Module | create_invoice() | Module Sub Procedure | Open the form FormOpenInvoice |
| Create Invoices | Button | Invoice sheet | FormOpenInvoice | Userform | Userform_Initialize() | Userform Event | Populate the list box in FormOpenInvoice with a list of open invoices before displaying the form |
| Create Invoices | Button | Invoice sheet | FormOpenInvoice | Userform | btCreateInvoice_Click() | Userform Event | Create invoices in both PDF and Excel format |
| Create Invoices | Button | Invoice sheet | FormOpenInvoice | Userform | btCancel_Click() | Userform Event | Exit and Close the form |
| Create Invoices | Button | Invoice sheet | FormOpenInvoice | Userform | btCreateEmail_Click() | Userform Event | Create an email to customer with PDF invoice attached |
| Create Invoices | Button | Invoice sheet | FormOpenInvoice | Userform | btNotNow_Click() | Userform Event | Exit and Close the form |
| Collapse Fields | Check Box | Invoice sheet | shInvoice | Worksheet | axCollapse_Click() | ActiveX Control Event | Hide/unhide columns that show low-level details |
| Hide Paid Invoices | Check Box | Invoice sheet | shInvoice | Worksheet | axHide_Click() | ActiveX Control Event | Hide/unhide the rows where invoices are already paid |
| View Invoices | Button | Dashboard sheet | MainInvoiceGenerator | Module | view_invoices() | Module Sub Procedure | Navigate to the "Invoice" sheet to see the list of invoices |
| Back to Dashboard | Arrow Shape | Invoice sheet | MainInvoiceGenerator | Module | return_dashboard() | Module Sub Procedure | Navigate back to Dashboard |
To edit the VBA code, open the VBA editor by pressing Alt + F11 key. This will provide access to the full range of underlying VBA code that drives the programs. Utilize the VBA Code Glossary provided above as a reference to navigate to the specific code you want to customize. Make the desired changes and adjustments as per your requirements.
To access the code related to a particular form control button, right-click the button and choose "Assign Macro" -> "Edit". This will open the VBA editor directly to the code associated with the button, allowing you to modify its functionality. To import a VBA program to your own workbook, open the VBA editor, go to "File" -> "Import File" and select a VBA code file from the downloaded VBA Code folder.
Contributions to the Invoice Generator Tool are welcome! If you have any suggestions, improvements, or bug fixes, please feel free to submit a pull request.
The Invoice Generator Tool is released under the MIT License. Feel free to use, modify, and distribute the code in this repository.
I hope you find the Invoice Generator tool helpful in simplifying your invoicing process and providing valuable insights into your business.