Using VBA for vLook up then to Populate Drop Down

[excelenergy]

Hello,</SPAN>
I attached a document...Thanks for your time in advance....

My question is this...Im unfamilar with how to do dynamic drop downs based on look-upped up values in Excel. So let me explain what I mean...when you open my document, on the userform there are two categories “Category 1” and “Category 2” – based on the two selections from those drop downs I want Excel to populate a third drop down called “Category 3”, but I want it to populate the third drop down with the "Number, and Colour" that corresponds to the combination selected in the first two drop downs. So all the look up values are on “Sheet2”...I know the vlookup formula but how do I get Excel to populate a third drop down with a colour, and number value - based on the values from the two above drop downs above it? </SPAN>

(It it is of any value, these are the different combinations for the different colours), but you can see these values in a chart by opening the attachment and selecting “sheet2”:

http://www.filedropper.com/inputtemplate-sampledata

These combinations would make the third drop down populate green
C1 and L1, or L2, or L3, or L4,
C2 and L1, or L2, or L3
C3 and L1, or L2
C1 and L1

These combinations would make it populate orange:
C2 and L6
C3 and L5
C4 and L4
C5 and L3
C6 and L2

These combinations for yellow:
C1 and L5
C1 and L6
C2 and L4
C2 and L5
C3 and L3
C3 and L4
C4 and L2
C4 and L3
C5 and L1
C5 and L2
C6 and L1

These combinations to populate the colour red:
C3 and L6
C4 and L5
C4 and L6
C5 and L4
C5 and L5
C5 and L6
C6 and L3
C6 and L4
C6 and L5
C6 and L6</SPAN></SPAN>

 

[RJPotts]

My question is this...Im unfamilar with how to do dynamic drop downs based on look-upped up values in Excel. So let me explain what I mean...when you open my document, on the userform there are two categories “Category 1” and “Category 2” – based on the two selections from those drop downs I want Excel to populate a third drop down called “Category 3”, but I want it to populate the third drop down with the "Number, and Colour" that corresponds to the combination selected in the first two drop downs. So all the look up values are on “Sheet2”...I know the vlookup formula but how do I get Excel to populate a third drop down with a colour, and number value - based on the values from the two above drop downs above it?

When you refer to the colours to populate the third combobox do you mean that you want the name of the colour to appear in the combobox or you want it to populate with a range named after that colour? If it's the former then is a combobox necessary? If it's the latter then presumably the details of those ranges is on your linked file? (I'm unable to d/l / open that until I'm home).

Best regards
Richard

 

[excelenergy]

Hey Pal,

Thanks for your response. The drop downs are part of functional requirements for an application. I tried to deviate from doing the drop-down thing, but its what they want. To answer your question, I would like the third drop down box to populate with the actual colour, and the number that corresponds to that colour. Then once the script matches a number

Once your home, youll see on sheet two that each colour (yellow, green, red, orange) has a number (I, II, III, or IV), so based on the two drop downs above, Id like it to find and locate that colour & number based on the user selection in the prior two drop downs. Then once the colour is found, Id like the actual drop down to change from being blank to yellow, green, etc..

When you refer to the colours to populate the third combobox do you mean that you want the name of the colour to appear in the combobox or you want it to populate with a range named after that colour? If it's the former then is a combobox necessary? If it's the latter then presumably the details of those ranges is on your linked file? (I'm unable to d/l / open that until I'm home).

Best regards
Richard

 

[RJPotts]

To answer your question, I would like the third drop down box to populate with the actual colour, and the number that corresponds to that colour.

So the combobox will be coloured (as opposed to displaying the colour name) and the text in that combobox will state the number?

best regards
Richard

 

[excelenergy]

Hello again,

Absolutely, yep! It should be coloured.

So the combobox will be coloured (as opposed to displaying the colour name) and the text in that combobox will state the number?

best regards
Richard

 

[RJPotts]

I've downloaded your file, however, I had a couple of slight issues;

For increased compatibility it would be best to save as Excel 2003 format files. I discovered that this machine (not my primary PC) which is running 2003 didnt have the 2007 compatibility pack installed so it was necessary to do so.

The file you uploaded has macros, no problem there, I opened with them disabled so I could have a look at them before letting them run. But, I couldnt because the vba was protected.........

So I'm working a little blind. I have though created an example which seems to do what you're looking for. Private message me your address and I'll email the file to you. If you could then confirm if its what you need to do and I'll post the code here for others.

Oh, one question...... ComboBox3, should the dropdown be available to use? I.e. should it have the list of I, II, III & IV or should it be blank with just the value auto-populated by the other two comboboxes?

Best regards
Richard