x2 COMBO Boxes to Find price VBA Code

[sara101]

Hi all,

I have X 2 combo Boxes and a text box,

What I need is When the first combo box is selected it shows the relevant details in combo box 2, then when combo box 2 is selected it shows the Information in the text box,

Example..

Combo box 1 - List = MILES [5,10,15,20,25,30] E.T.C
Combo Box 2 - List - Location [RTW AGG Plants, RTW Linford, RTW Alfreton]
Text box 1 to show related price, based on miles & location.

Thanks

[MickG]

See example in sheet1 ;-

[sara101]

Thanks Mick G - but how can I determine what text shows based on combo box 1 and 2, Your example just shows Combo box 2 Result in the text box? Is that correct ?

[MickG]

Change the code in the sheet Module as below to add Combobox1 as shown:-

Please Login or Register  to view this content.

[sara101]

I think im confusing you,

I need example,
Combo box 1 - Say I chose 5 miles from the selection, - Then combo box 2- say I choose Linford as the location, the text box is to show £300.00
But if I chose 5 miles from Combo box 1 but say Alfreton from combo box 2 the price changes to £375.00 ?

Thanks

[MickG]

Where is the list of prices based on Location and Miles
Please show list of "Miles,Location,Price"

[sara101]

see attached my Workbook, Thanks. See sheet FDC -

Column A with miles -
Colum B - 3 plant Locations [Repeated for each variable of miles]
Column C- price
So 5 miles has 3 different price's depending on what location is Location Selected.

User form Labelled Boxes.

Combo Box 1 - CBOfdm
Combo Box 2 - cboplantlocation
Text box - Txtfdc

Thank you

[MickG]

Try this:-
NB:- I had a intermittent problem opening "Userform1" so I added an "CommandButton", but you may find your original "Button" works perfectly OK !!!

[sara101]

Thanks Mick, Sorry about the confusion. I attached the wrong workbook earlia, this is the most uptodate one, So sorry -Any chance you could swap the code over to this workbook? I have tried but im getting a code error? =( Thanks in Advance for your time.

[MickG]

Try this:-

[sara101]

Thank you so much .... So much Appreciated....

Do you know how to Automatically Get the userfrom to input a reference for each record..
Example 001, then when I press ok and the record saves and I enter a new one the New reference number become 002 Automatically.

Also if I close the form and then re-open it picks up the last available reference number.?
Thanks

[sara101]

My Box for this is TEXTURN. and Its column A on my Sheet? Thanks in advance

[MickG]

Perhaps this:-
"See "Sheet Activate" code and "CommandButton" "OK" code.
Numbers logged in sheet "Master" col "A"

[sara101]

Thanks Mick, But its not writing any of the data to my sheet when I press "OK" ? its generating the number though ?

Thanks

[MickG]

I "Remarked" out the other code in the "OK" button so you could fit it around the Numbering code.
I can see that there is a lot of work on that Userform, far beyond being able to point you in the right direction !!!

[sara101]

Oh right ok, Im stuck then now Thanks for your help so far anyway.