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.
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.
- School Name
- Accommodation Type
- Yes/No Airport pick up
- Yes/No Visa Fees
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.
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 …
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.
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: