DropDown x 2
Posted by Osman P. on August 27, 2001 5:00 AM
Hello,
i have some strange request, and will try it to explain even if i am not that good in english. I want to make 2 DropDown Lists where second one depends on first one. Here some example for what i will (btw. i do know how to make dropdown menu), let us say i have one dropdown with A, B and C. Now, second dropdown should check if A, B or C is chosen in first menu, and makes following list... if A is chosen "me;you;he", if B is chosen then it should give following "We;You;They" or something. Ofcourse i need both to be dropdown. Thanks in advance ...
Posted by Robb on August 27, 2001 5:40 AM
Osman
There may be other ways but, assuming drop-down is a ComboBox, try this:
If ComboBoxes are Forms Menu/Shapes, they get there values from cells.
If the first ComboBox has Input Range A1:A3, then these will be,say, A,B and C
Let's assume you make the cell link (output) A5
Now make the Input Range for the second ComboBox B1:B3
Make the cell link (output) anywhere
Depending which value the user selects, the output to A5 will be 1,2 or 3
In B1 put the formula =IF(A5=1,"Me",IF(A5=2,"You",IF(A5=3,"He","")))
In B2 " " " =IF(A5=1,"We",IF(A5=2,"Them",IF(A5=3,"They","")))
In B3 " " " =IF(A5=1,"Tom",IF(A5=2,"Fred",IF(A5=3,"Helen","")))
Values in the second box should now depend on selection in the first.
If you are using an activex control, you will need to add some code. Try this in the exit event of the first box:
Assuming Box1 has controlsource Sheet1 A1
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ComboBox2.Clear
cb1 = Worksheets("Sheet1").Range("A1")
Select Case cb1
Case "A"
ComboBox2.AddItem "Me"
ComboBox2.AddItem "You"
ComboBox2.AddItem "He"
Case "B"
ComboBox2.AddItem "We"
ComboBox2.AddItem "Them"
ComboBox2.AddItem "They"
Case "C"
ComboBox2.AddItem "Tom"
ComboBox2.AddItem "Fred"
ComboBox2.AddItem "Helen"
Case Else
Exit Sub
End Select
End Sub
Any help?
Posted by Aladin Akyurek on August 27, 2001 6:11 AM
Or...
just use Data|Validation
In a separate worksheet, create your sublists in say columns B (from B2 on),C (from C2 on), D (from D2 on) and so on. Select the items in B and name it e.g., ListA via the Name Box (see the Formula Bar). Select the range in C and name it ListB, so on.
Create in column A from A2 on the following list:
ListA
ListB
ListC
etc.
Select the range in A and name it MainList via the Name Box.
Go to the worksheet (another sheet) where you want the drop down boxes, say in A4 and B4.
Activate A4.
Activate the option Data|Validation.
Select Custom (or List) for Allow.
Enter the following formula for Formula (or Source):
=MainList
Click OK.
Activate B4.
Activate Data|Validation.
Select Custom for Allow.
Enter the following for Formula:
=INDIRECT(A4)
Click OK.
Aladin
Posted by Osman on August 27, 2001 7:02 AM
i know you guys trying to explain me how to do it, but i am affraid i do not understand it or betetr said i have german system so not same stuff to choose, i will try to explain how i tried to do, gone to Data->Validation and so i made my DropDown (dunno if it is ComboBox in english), i even tried to make it like this, to fill one cell with infos like (me;you;he) (we have ; instead of ,)and link it over validation, the error was output was like one thing to choose and looked like (me;you;he) and not 3 things, is here maybe some help, or try pls to explain it clear i didn#t understand a sinlge word from you Aladin, thx
Posted by Aladin Akyurek on August 27, 2001 7:33 AM
> try pls to explain it clear i didn#t understand a sinlge word from you Aladin, thx
Not a single word? Hmm...
Anyway, I shouldn't have talked about Custom & Formula, instead kept it to just List & Source.
I've sent you the workbook that shows how it's done with Data|validation.
Hope that helps.
Aladin
