Dynamic Invoice Creator (Excel VBA)
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.CountrySchool NameCoursesAccommodation TypeYes/No Airport pick upYes/No Visa Fees…Based on the country the user chooses from the country drop downlist, 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.Main InterfaceThe 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 …Country drop down listNext 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.School drop down listNext 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.Course type drop down listAccommodation type drop down listAlso there are many Yes/No fields.Yes/No FieldsThe 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:InputInputInputInput