Selection from one drop down creates new drop down

D

davethacker

New Member
Joined
Jul 20, 2003
Messages
14
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
ABCDEFGHIJKLMNOP
1
2SelectDoorStyle
3SelectWoodSpecie
4SelectAvailablestain
5Doorfactor
6AvailablewoodspeciesDoorstylesAvailablewoodspecies
7OakAlderMapleCherryOakAlderMapleCherry
8AvailableStainsNaturalNaturalNaturalNaturalCharleston1.481.481.531.65Doorfactor
9AutumnGoldenGoldenGoldenConcord1.31.31.361.42
10NutmegAutumnAutumnAutumnBoston1.421.421.481.59
11JavaSiennaSiennaSavannah1.361.361.421.48
12PacificaBrandyBrandyDakota1.481.481.531.59
13JuniperCinnamonCinnamonWyoming1.361.361.421.48
14GraphiteNutmegNutmegColorado1.531.531.591.65
15JavaMontana1.421.421.481.53
16
Sheet1
[/code]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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.

Code: 
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
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
366
Jeffrey Mahoney
Jeffrey Mahoney
T
  • Question
Show different values to drop down box
Replies
1
Views
239
Scott Huish
Scott Huish
K
  • Solved
I have code that works for creating a drop-down list on a WORKSHEET that allows you to select multiple items... can this also be done on a USERFORM?
Replies
2
Views
242
kbishop94
K
D
  • Solved
Drop down list to display only grouped items
2
Replies
11
Views
345
Peter_SSs
Peter_SSs
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
673
MissDB101
M

Forum statistics

Threads
1,216,126
Messages
6,129,008
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top