PreProcessing with VBA

[MontyMinh]

• [x] 1. Create a Template sheet with Product (vertical) and Factory (horizontal).
• [x] 2. Create a sub to copy the template file, then delete the template file
• [x] 3. Sort the Product/Factory list before generating template
• [x] 4. Check that the number of constraints is valid (not 0, so basically just verify input and loop until the user put something valid in)
• [ ] 5. Check that all the products are mentioned in constraints (Verify Constraints button)
• [x] 6. Demand Volume and Outbound Cost file (sorted)
• [x] 7. Delete blank rows in Product and Factory List
• [x] 8. Button to Undo Generation
• [x] 9. Button to Restore Worksheet
• [x] 10. MsgBox when the user click on the box, it will ask them if they want to generate the format
• [x] 11. Lock Product and Factory List sheets
• [x] 12. Think of a way to make all the macros more accessible
• [x] 13. Check if the Factory/Product List is not blank before generation
• [ ] 14. Create an additional sheet at the beginning for Instructions and Notes
• [ ] 15. Add a button to automatically sort Sales Volume & Outbound Cost by product name A-Z

[MontyMinh]

For task 1, the list of name to copy to is as follow

1. Factory Per Product
2. Inbound Cost Per Product
3. Efficiency Per product

[MontyMinh]

The rest of the sheets are

1. Outbound Cost Per Product
2. Capacity Constraints
3. Supply Constraints
4. Capacity Volume
5. Demand Volume

For these, 2, 3, 4 has a similar layout and 1, 5 has similar layout

[MontyMinh]

Now I finished 2, 3, 4

I still need to do 1, 5

[MontyMinh]

Tomorrow I need to

• [x] 3. Sort the Product/Factory list before generating template
• [ ] 4. Check that the number of constraints is valid (not 0, so basically just verify input and loop until the user put something valid in)
• [ ] 5. Check that all the products are mentioned in constraints (Verify Constraints button)
• [x] 6. Demand Volume and Outbound Cost file (sorted)
• [ ] 7. Delete blank rows in Product and Factory List

[MontyMinh]

I was thinking we would also need a button to just return the whole worksheet to original format with just product and factory

• [ ] 8. Button to return the worksheet to the original form

[MontyMinh]

I'm also thinking about a message before the user run that ask them if they want to run the template. (9.) And also tell them about the option to reset the workbook to before (8.)

Also I need to find out how to lock the product list / factory list sheets (10.)

[MontyMinh]

10. I'm just gonna assume that users are not complete idiots and delete Factory and Product List

[MontyMinh]

I think I will do 11 tomorrow and delay 4, 5 to the week after

[MontyMinh]

• [x] 11. Think of a way to make all the macros more accessible

Added macros to quick access toolbar

[MontyMinh]

For 4 I just make it so that if the input is invalid or cancel or close, then number of constraints is 1 and the user have to input the data manually

[MontyMinh]

For 11 I think this could includes the stuff in User Guide and additionally, what kind of inputs we would like for each sheet