PDA

View Full Version : Advice Needed



bopo
01-11-2007, 01:58 PM
Hi People :hi:

Basically I have a form, and I want the user to click a button.

When the user clicks the button, a spreadsheet appears with all the required details and results of a calculation (dont know where to put the coding for this)

But where do I put the coding for this, behind the button on the form or spreadsheet or something, because I want things calculated and the results to appear on the spreadsheet.

Brandtrock
01-11-2007, 02:18 PM
Hi People :hi:

Basically I have a form, and I want the user to click a button.

When the user clicks the button, a spreadsheet appears with all the required details and results of a calculation (dont know where to put the coding for this)

But where do I put the coding for this, behind the button on the form or spreadsheet or something, because I want things calculated and the results to appear on the spreadsheet.

Is the data on the form also?

or already in the spreadsheet?

What calculation needs to be performed?

In general, the Click event of the button could hold the code to perform the calculation, or call a seperate routine in a separate module. A few more details are required for a more robust and complete answer.

Regards,

bopo
01-11-2007, 03:16 PM
The data will be on the form, as users will enter it via text boxes, there is no data on the spreadsheet, I want the data on the form to display on the spreadsheet and the calculations to be performed & also display on the spreadsheet.

Thanks for your help

Brandtrock
01-11-2007, 03:37 PM
Well then, here is a tiny example of how this type of thing can work.

It is VERY BASIC. No bells and whistles at all. When you see how it works, then give it a go on your own and post back if you have any more questions.

No calculations included yet though.

Regards,

Brandtrock
01-11-2007, 03:39 PM
If you want specific help with your form, post a small example workbook using Manage attachments in the reply to thread window (scroll down a bit).

Brandtrock
01-11-2007, 10:00 PM
bopo has sent a file via PM to me containing the layout of the project related to this question. The calculations required are Accomodation price * per person and Activity price * per person.

I have suggested the following:


The file you attached has been received and reviewed. The sheet named Invoice-Form is a worksheet, not a form in the sense I was thinking (userform). I assume that the information on the Act Data and Accom Data sheets is where you will be getting the accommodation and activity cost figures. Having said that, designing a userform is your next step.

A form with a combobox or listbox for the accomodation costs, a combobox or listbox for the activity cost, a textbox for entering the number of individuals (or maybe one each for both activity and accomodation if they can be different numbers), a submit button, and a cancel button would be my recommendation. To create the form, press Alt+F11 to launch the VBE.

You will find your VBAProject in the list on the left. Highlight the name of your project by clicking it once then select Insert>Form from the top menu. Now you can size the form and place the controls as you desire. There will be coding necessary behind each control to get it to function the way you desire (load the price lists, submit the information, cancel the form).

Format the Invoice-Form sheet the way you want it to look when you print it out. Sending the figures from the userform to the "right" spot on the form will be done once you have decided where the right place on the form is.

I have also directed bopo to include the file in a post to this thread and to please keep the requests for help in the thread so all can benefit from any help received.

Regards,