In a nutshell, I am trying to design a simple save/load functionality in Excel. What is the best way to go about this?
BACKGROUND:
I have a spreadsheet that creates invoice estimates for photoshoots based on line items. The input for each line is a combination of drop down boxes and user number entries (two photographers for half a day, equipment rental for a flat fee, etc.), and the spreadsheet calculates out the cost per line and the overall total cost. It works great.
What I'm building now is a set of pre-defined "packages" that can automatically be populated into the spreadsheet at the touch of a button. I think I've got this handled by using macros to clear out the data on the sheet and then apply the specific data I've chosen. The plan is to add a drop down at the top of the sheet that allows a user to choose a package(macro) by name and a button to apply(run) it. So I could select "Generic Photoshoot Package A" and the macro would clear the existing line items on the sheet and add in all of the line items associated with this package. I think I've got this part handled.
Now, the next step is where I'm stuck. What I need is the ability for a user to create a new package without having to write VB or do anything code-related. Basically a SAVE function, where they would set up the spreadsheet the way they like it and hit a SAVE button, and the new package is added to the drop down list as an option.
Is there an existing piece of functionality in Excel that can read data in from a sheet, store it in some form, and then spit it back out to the sheet upon request? Could I build a save function using VBA that would create a custom macro that contains the data it finds on the sheet?
BACKGROUND:
I have a spreadsheet that creates invoice estimates for photoshoots based on line items. The input for each line is a combination of drop down boxes and user number entries (two photographers for half a day, equipment rental for a flat fee, etc.), and the spreadsheet calculates out the cost per line and the overall total cost. It works great.
What I'm building now is a set of pre-defined "packages" that can automatically be populated into the spreadsheet at the touch of a button. I think I've got this handled by using macros to clear out the data on the sheet and then apply the specific data I've chosen. The plan is to add a drop down at the top of the sheet that allows a user to choose a package(macro) by name and a button to apply(run) it. So I could select "Generic Photoshoot Package A" and the macro would clear the existing line items on the sheet and add in all of the line items associated with this package. I think I've got this part handled.
Now, the next step is where I'm stuck. What I need is the ability for a user to create a new package without having to write VB or do anything code-related. Basically a SAVE function, where they would set up the spreadsheet the way they like it and hit a SAVE button, and the new package is added to the drop down list as an option.
Is there an existing piece of functionality in Excel that can read data in from a sheet, store it in some form, and then spit it back out to the sheet upon request? Could I build a save function using VBA that would create a custom macro that contains the data it finds on the sheet?
