multipage userforms

Hi,

I'm creating a userform with multipage controls, (I've attached a couple of images). I'd like to be able to manually enter a customers details in the search frame and if the customer exists, I'd like the form to populate the frame to the right with the customer contact details and the frame named "Related jobs" on the second page "job details"

Is it possible to display data related to the customer record and which control would I use for it? Would there have to be separate text fields for it to populate or would it draw the spreadsheet into the userform? I'd like to use a userform for this, though I know it would be possible to do it using formulae in a separate worksheet...

Finally, I'd also like to add in a quote calculator to the userform as a third page, though this would be more easily done on a worksheet rather than a userform I would have thought. Still, i'd like to know if this is possible?

Any help would be much appreciated!

master userform - customers.png
master userform - jobs.png

Thanks


Mick

Hello,

Could you please attach an example of you workbook? It's a little easier to build the code when you know where it's referencing.

Thanks,

RVASQUEZ

Thanks for looking into this!

Unfortunately the file is 8.7MB so it won't upload. I've created a basic workbook with just the column headings.

Can you work with this?

Thanks


Mick

I can work with this.

You have the following on your userform:
Customer Name
Customer ID
Customer Details
New customer details
Now Job Details
Related jobs

If you could please tell me for each of the categories on the form what you would like to pull into those spaces from the workbook that would help. Customer Name and ID are obvious but the other are a little vague considering the amount of columns you have.

Thanks!

Hi rvasquez,

I've been working on the design for the userforms, I think they might help with your question...

Customer details v2.jpg
Job details v2.jpg
Quote calculator.jpg

The idea was that the user could enter either the customer name or the customer ID into the form and it would retrieve the relevant customer details i.e address details - 1st line of address, city/town, post code as well as contact phone numbers and email address.
In the attached images I've also included the related jobs for existing customer frame where I'd like any previous jobs relating to this particular customer to be displayed.

Then, if there is no existing customer in the database then the user can fill in the New Customer details section with the required details.

For the job details page, I'd like the user to be able to insert the details of any new job that is requested.

The quote calculator then generates a quote based upon the details given in the new collection details frame, this would be a function of the collection type (A), the distance of the collection address from a specified address (B), the type and number of heavy items collected (Hv), the type and number of hazardous items collected (Hz) and then whether any discount is to be applied (D) the equation would be = (A+B+Hv+Hz)*D.

Thanks


Mick

ignore the attached image, it's a previous version of the customer details v2 image linked to in the text.... don't know what happened there!

Hey mickisme,

I can work with the screen shots you've given me but it worries me that you said previously that you are developing the format of the form. So I'm asking that you please leave the forms as the last screen shot you gave me that way I can give you a code. If you wish to keep altering the form please decide on a final format and then do as royUK has asked and attach a spreadsheet with the Userform and the columns in it. For now I'll continue working with what you have given me.

Thanks!

Hi RoyUK, rvasquez,

Apologies, I had tried to upload the whole workbook with all the VB projects and data but at 8.7MB it wouldn't upload. Can you suggest a way to share it?

rvasquez, I'll leave it as it is now, I really appreciate you having a look at it for me!

Thanks


Mick

Ok, I've exported the form file and imported it into a new work which also contains the raw data for the workbook, I think for some reason the other macros in the workbook were badly written and bumping up the file size.

Thanks


Mick

Hi RoyUK,

Ok, not sure why that happened! I had tried zipping the file but only managed to compress it to 6MB ish. I've created a copy in my public dropbox folder here, I hope it works now!

Thanks


Mick

Hello mickisme,

Sorry it took me so long been a little busy with work. Attached you will your updated worksheet. I have set the userform to show when you select the first worksheet and also when the workbook opens. Remember you need to enable content and macros in order for the workbook to work correctly. Also, I wasn't quite sure how to do the quote calculator so although there is a tab for this in the userform it is not set to perform any calculations.

I have placed comments within the code that explain exactly what the code does.

Please let me know if you have any questions.

Thanks!

RVASQUEZ

Wow, that is amazing. Thank you rvasquez, I really appreciate your help with this.