Dynamic Invoice Creator (Excel VBA)

This was a VBA for Excel project for creating invoices. The user had several drop down lists to choose from. Based on the drop down  lists selected the invoice would be created. Also based on the values selected from one drop down list, the available values in the other drop down lists would change.

Project Descriptions:
In this project the client wanted a an excel macro to create invoices. This project was to create invoices for students wanting to enter college. There were several drop down lists where the user would choose values from.

  • Country
  • School Name
  • Courses
  • Accommodation Type
  • Yes/No Airport pick up
  • Yes/No Visa Fees
Based on the country the user chooses from the country drop down list, all other drop down lists would change. So for example if the user picks “Canada” from the country drop down list, the school name drop down list would update with only Canadian school names. Once the user chooses a school then the different courses associated with that school would appear in the course type drop down list. Based on the choices the user would make the prices were automatically updated.

The client also wanted to be able to provide the data to the program any way he wants. For example:

  • Country A could have 3 schools where country B could have 5 schools.
  • School A could have 7 course types while school B could have only 2
  • ….

This project was completed using VBA for Excel. The Worksheet_Change event was used to update prices automatically. In order for the end user not mess up the sheet all cells were locked, and the end user will only be able to select values from the drop down lists and modify a few selected cells.

Here you can see the programs main interface:
2

The user selects a country from the drop down list. Based on the value the user selects the school drop down list is updated. For example if the user selects US, then the school drop down list will have MIT, Stanford, … If the user selects Canada the school drop down list will be populated with the value, Toronto University, UBC …
3

 

Next the user selects the school from the drop down list. Based on the value selected the State and City fields are automatically updated. Also the accommodation type and course type drop lists are updated to based on the courses and accommodations available for that school.
4

Next the user selects the course type and the accommodation type from their respective drop down lists. After inputting a duration for them their price fields are updated automatically.
5

6

Also there are many Yes/No fields.
7

The values in the drop lists come from predefined data set by the programs admin. Below you can see some sample data used for this invoice:

Input

Input

Input

Input

Input

Input

Input

Input

 

 

 

Live Chat