Guidance and Direction

[SailFL]

I have created a UserForm for a spread sheet that is used to create a paver (like a drive way paver) proposal. The CostCalc spread sheet calculates and records all the information for a complete proposal. So my UserForm has 6 tab pages to capture all the data. The UserForm was created because 1) to make it easier for people to enter the data and 2) to hide the calculations that are performed. I have a print button on the UserForm that will print out the CostCalc spread sheet which has a proposal form as one of its sheets.

Programming I know how to do. I am recently new to VBA but I am learning quickly. I am not sure how to do some things that I want to do so that is the purpose of this thread. I need some suggestions. I also want the input of those that have more experience at this.

1) I want to use the CostCalc spread sheet in the back ground to actually do the calculations that will be displayed on the UserForm as the quantities are inputed in the UserForm. I want that CostCalc to be hidden. Where do I look at to do this?

2) I am thinking that as I user inputs Data to the UserForm, I will write the input to the spread sheet to get the calculations that will be displayed in the UserForm. Is this easily done? Will the response be quick?

3) When the user is done inputing data, I want to save the template CostCalc spread sheet to a new one with a spread sheet saved named by the project number. I assume that I will SaveAs? And I want the Template CostCalc sheet to be new so that the user can input another proposal.

4) Are there some thngs that I need to consider for such a under taking?

Thank you for your responses!

[mdmackillop]

Hi Nils,
I've tried to answer your questions in this example.

You might also want to consider:
AutoNumbering the Order numbers
Enabling the form (or a similar one) to display details from a saved order by entering the number on the form
Protecting the saved sheets to prevent accidental changes. Maybe allow these only by using the form.
Regards
Malcolm

[mdmackillop]

A further thought, maybe for the future, is to create a routine to extract appropriate data from the order and create a mailmerge document within Word for sending to your Clients. Although you can produce Reports or Letters within Excel, they rarely format properly, unless very simple.

[Bob Phillips]

How about creating an add-in. The userform can be defined to the add-in, as can the CostCalc spreadsheet. Being in an add-in, the CoistCalc spreadsheet will not be seen, and can be copied at the end as saved as a new workbook.

[royUK]

Why do the calculations on the sheet. They can be done ithin the VBA

[SailFL]

Yes the calculations could be done in the application but why when you have a spread sheet that works well. I am just creating a user interface. If I was writting this in anything but VBA, I would store the data. Another advanatage is that the manager can look at the data in another format if he wants to.