Selection from one drop down creates new drop down

[davethacker]

In the attached example I am trying to get the selections from one drop down to gnerate available selections in the next. The first drop down is the door style. This then needs to generate the possible drop downs in wood species. This then needs to generate the possible drop down in available stains. this then needs to generate the related door factor. apreciate any help that you can give.

Thanks,
Dave

Excel_validation_problem.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	SelectDoorStyle
3	SelectWoodSpecie
4	SelectAvailablestain
5	Doorfactor
6					Availablewoodspecies					Doorstyles	Availablewoodspecies
7					Oak	Alder	Maple	Cherry			Oak	Alder	Maple	Cherry
8				AvailableStains	Natural	Natural	Natural	Natural		Charleston	1.48	1.48	1.53	1.65	Doorfactor
9					Autumn	Golden	Golden	Golden		Concord	1.3	1.3	1.36	1.42
10					Nutmeg	Autumn	Autumn	Autumn		Boston	1.42	1.42	1.48	1.59
11					Java	Sienna		Sienna		Savannah	1.36	1.36	1.42	1.48
12					Pacifica	Brandy		Brandy		Dakota	1.48	1.48	1.53	1.59
13					Juniper	Cinnamon		Cinnamon		Wyoming	1.36	1.36	1.42	1.48
14					Graphite	Nutmeg		Nutmeg		Colorado	1.53	1.53	1.59	1.65
15								Java		Montana	1.42	1.42	1.48	1.53
16
Sheet1

[/code]

 

[Von Pookie]

See Aladin's post here. It may get you started:
http://www.mrexcel.com/board2/viewtopic.php?p=154#154

 

[qmwhitlock]

I am very new to excel, but I think I figured out how to do this. To test I populated sheet1 as follows:
A1-A4=one, two, three, four
B1-B4=blank
C1-C4=horse,cat,dog,mouse
D1-D4=train, car, boat, truck
E1-E4=rain., snow, sun, cloud
F1-F4=red, blue, green, yellow
insert a combobox1 and listbox1 on the sheet
in properties for combobox1, listfillrange=Sheet1!A1:A4
in properties for listbox1, listfillrange=Sheet1!B1:B4
open your VBE by doubleclicking combobox1, then:


Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
Case Is = "one"
Worksheets("sheet1").Range("b1") = Worksheets("sheet1").Range("f1")
Worksheets("sheet1").Range("b2") = Worksheets("sheet1").Range("f2")
Worksheets("sheet1").Range("b3") = Worksheets("sheet1").Range("f3")
Worksheets("sheet1").Range("b4") = Worksheets("sheet1").Range("f4")
Case Is = "two "
Worksheets("sheet1").Range("b1") = Worksheets("sheet1").Range("c1")
Worksheets("sheet1").Range("b2") = Worksheets("sheet1").Range("c2")
Worksheets("sheet1").Range("b3") = Worksheets("sheet1").Range("c3")
Worksheets("sheet1").Range("b4") = Worksheets("sheet1").Range("c4")
Case Is = "three"
Worksheets("sheet1").Range("b1") = Worksheets("sheet1").Range("d1")
Worksheets("sheet1").Range("b2") = Worksheets("sheet1").Range("d2")
Worksheets("sheet1").Range("b3") = Worksheets("sheet1").Range("d3")
Worksheets("sheet1").Range("b4") = Worksheets("sheet1").Range("d4")
Case Is = "four"
Worksheets("sheet1").Range("b1") = Worksheets("sheet1").Range("e1")
Worksheets("sheet1").Range("b2") = Worksheets("sheet1").Range("e2")
Worksheets("sheet1").Range("b3") = Worksheets("sheet1").Range("e3")
Worksheets("sheet1").Range("b4") = Worksheets("sheet1").Range("e4")
End Select
End Sub


I know there is a much shorter way to move from one column to another, maybe by just changing the listbox1.listfillrange to whichever column you wanted. sorry I cant test that more, but I gotta run to work now. Good luck
Quinten

 

[davethacker]

Kristy, Quinten,
Thanks for the great help.

Kristy, I ran Aladen's solution and it works on all but giving me the door factor. My issue now is that I have one drop down that gives the door selection. That selection then provided a drop down list for wood species that allows me to select the available specie. That selection then provideds a drop down list in the stain selection box for the stains that are available. The final equation that I need is for the door factor to show in one more box based upon the doorstyle and wood selections. Is that possible? You can see the door factors in the table in my example.

Quinten,
For someone new you are very smart. I didn't run your solution. I will try it now. Maybe it will give me what I am after above. Obviously you are doing some VB code. I don't understand much about that although I have copied and pasted some code from past help on this site. I still use that soulution daily. If you have more input it will be much appreciated.

Again, Thank you both for your help.

 

[qmwhitlock]

Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
Case Is = "one"
listbox1.listfillrange="sheet1!F1:F4"
Case Is = "two "
listbox1.listfillrange="sheet1!C1:C4"
Case Is = "three"
listbox1.listfillrange="sheet1!D1:D4"
Case Is = "four"
listbox1.listfillrange="sheet1!E1:E4"
End Select
End Sub

this is much shorter, and should work. you would have to alter this for all your listboxes as well. Goodluck
Quinten

 

[davethacker]

drop down box issues

Quinten,
Thanks for the help. I haven't been able to Make your solution work. That is probably because i don't understand enough about the Visual Basic. I still need to be able to take the results of the drop down box's I have created and use those combined selections to generate the door factor from my table. I can do this with formulas except I run out pretty quickly as it only allows seven iterations before the formulas bar is full. I have thirty two possibilities. Is there a way to write the formula in VB code and have the box refernce that?

Thanks again,
Dave

 

[Von Pookie]

You do not need VBA to do this.

All you need is one formula, some named ranges, and data validation.

I'll go through this step by step, using the setup you posted originally.

1. Select J8:J15. Name this range DoorStyles
   
2. Select E7:H7. Name this range Species.
   
3. Select E8:E14. Name this range StainOak.
   
4. Select F8:F14. Name this range StainAlder.
   
5. Select G8:G10. Name this range StainMaple.
   
6. Select H8:H15. Name this range StainCherry.
   
7. Select B2. Go to Data > Validation. Select "List" from the criteria dropdown. Set the source as =DoorStyles. Click OK.
   
8. Select B3. Go to Data > Validation. Select "List" from the criteria dropdown. Set the source as =Species. Click OK.
   
9. Select B4. Go to Data > Validation. Select "List" from the criteria dropdown. Set the source as =INDIRECT("Stain"&$B$3). Click OK.
   
10. Select B5. Enter this formula:
    =IF(OR($B$2="",$B$3=""),"",INDEX($K$8:$N$15,MATCH($B$2,DoorStyles,0),MATCH($B$3,$K$7:$N$7,0)))

You should then be good to go!

 

[qmwhitlock]

OK, I think I may have it. it consists to 2 worksheets, the first has your controls, the second has the lists that are going to be used. Hopefully I understood correctly by the diagram you put on your first post that the stains are dependant on which wood is used only, not on the style of door. and the door factor is a function of which style door and which wood is used. Again I am very new to this and I was once told it doesnt matter what it looks like as long as it works, so it isnt very pretty, but you can probably beautify it up a bit. I will attach 2 screen shots, the combobox in B2 is combobox1, B3 is box2 and B4 is box3

if you double click on combobox1 it will open your VBE, and it will already have "Private Sub ComboBox1_Change() End Sub", just erase that, and copy and paste the below and everything should work, if you would rather me e-mail this excel file, let me know.

Private Sub ComboBox1_Change()
'this just puts the value you chose into cell B9
ActiveSheet.Range("b9") = ComboBox1.Value
'the following will select the door factor depending on the door style
'and the wood chosen. And it will put this value in cell B12.
'it will only do this if there is already a wood chosen
If ComboBox2.Value <> "" Then
Select Case ComboBox1.Value
Case Is = "charleston"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.53
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.65
    End Select
Case Is = "concord"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.3
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.3
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.42
    End Select
Case Is = "boston"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.59
    End Select
Case Is = "savannah"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.48
    End Select
Case Is = "dakota"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.53
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.59
    End Select
Case Is = "wyoming"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.48
    End Select
Case Is = "colorado"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.53
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.53
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.59
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.65
    End Select
Case Is = "montana"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.53
    End Select
    End Select
    End If
    
    
End Sub

Private Sub ComboBox2_Change()
'the following will look at what you select as the wood, and will populate
'the stain dropdown menu according to your selection.
Select Case ComboBox2.Value
Case Is = "oak"
ComboBox3.ListFillRange = "Sheet2!C2:C8"
Case Is = "alder"
ComboBox3.ListFillRange = "Sheet2!D2:D8"
Case Is = "maple"
ComboBox3.ListFillRange = "Sheet2!E2:E4"
Case Is = "cherry"
ComboBox3.ListFillRange = "Sheet2!F2:F9"
End Select
ActiveSheet.Range("b10") = ComboBox2.Value
'the following will select the door factor depending on the door style
'and the wood chosen. And it will put this value in cell B12.
'it will only do this if there is already a style chosen
If ComboBox1.Value <> "" Then
Select Case ComboBox1.Value
Case Is = "charleston"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.53
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.65
    End Select
Case Is = "concord"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.3
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.3
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.42
    End Select
Case Is = "boston"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.59
    End Select
Case Is = "savannah"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.48
    End Select
Case Is = "dakota"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.53
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.59
    End Select
Case Is = "wyoming"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.36
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.48
    End Select
Case Is = "colorado"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.53
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.53
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.59
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.65
    End Select
Case Is = "montana"
   Select Case ComboBox2.Value
    Case Is = "oak"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "alder"
      ActiveSheet.Range("b12") = 1.42
    Case Is = "maple"
      ActiveSheet.Range("b12") = 1.48
    Case Is = "cherry"
      ActiveSheet.Range("b12") = 1.53
    End Select
    End Select
    End If
End Sub

Private Sub ComboBox3_Change()
'this just selects the stain and puts it in Cell B11
ActiveSheet.Range("b11") = ComboBox3.Value
End Sub

 

[qmwhitlock]

ok, I forgot to add the images, and now I cant figure out how to add them. little help on this please.

sorry
quinten[/img][/url][/list][/list]

 

[Von Pookie]

Dude, just....no.

No offense, but you are making this WAY more complicated than it needs to be. You do not need VBA to do this. See my post above.