limited options from options

[kurai]

I would like to create a pulldown menu with options depending on a previously selected item.

I have a list of Items, descrptions, and prices- 3 different prices per item. I want to be able to select the item and in another column, select the prices available for that item. help me please

oh, and what is the maximum number of selections I can put in a pulldown listbox?

[Brian from Maui]

kurai,

Not sure if this is what you want

http://www.mrexcel.com/board/viewtop...c=7345&forum=2

[Joe Was]

You can also do this with a drop-down list, if your values are hard coded in a lookup list. The code below will use one list to lookup values in another list.

The first code is manual the second is automatic.

Sub BuildList()
'By Joe Was
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
Else
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End If
End Sub

To use the code!
Select an item in the dropdown in B1 (Like the: "State")
H column contains the list names for the lists in columns I, J & K, H would be the State list range name for the City list in column I, J & K or more column?
The list selected in B1 will set the dropdown values of D1 (Like the "Cities" in your selected State")

So you have one dropdown in B1 and the other in D1. When you select from the dropdown in B1 your selection loads the range name for the list used for the dropdown in D1. My test is B1 is three States and D1 is the Cities in that selected State. Hope this helps? JSW

P.S. The Private Sub automatically checks for a change in B1,s value and runs the macro to update D1.

[kurai]

I think I should have stated that I was using data validation not actual pull-downs.
I am sorry if this angers you... I ddnt realize you could do real ones in excel until I read a previous post.

[kurai]

I was reading on that post linked to earlier and I came across this

while I have been trying to do this, It dosnt seem to work
why????????


For an example let’s create two drop-downs with B1 being dependent on A1. That is to say what is displayed in A1 will determine what is available in the B1 drop-down list.

Step 1: Create 4 lists on Sheet2
A1=CPU
A2=Memory
A3=HDSZ

B1=P2
B2=P3
B3=P4

C1=128
C2=256
C3=512

D1=Gigbyte
D2=Terabyte

Step 2: Highlight each list and name it.
( naming a list is done with the “Insert” …”name” … toolbar command
2.a. The A:A list can be called anything ..this time lets call it “overview”
2.b. The B:B list Must be called “CPU”
2.c. The C:C list Must be called “Memory”
2.d. The D:D list must be called “HDSZ”

Step3: Go to Sheet1 to make the two drop-downs

Step4: Select sheet1!A1 and select “Data”… “validation” from toolbar commands

Step5: in the validation window the “Allow:” drop down = “lists” and in Source textbx type: “=overview” …. Press OK

Step6: To make the B1 drop down repeat step 4 & 5 ACCEPT in Source textbx type: “=INDIRECT(A1)”

Now Your Done !!!!!!!

[Chris Davison]

Kurai,

I believe Brian's link actually does give you all the info you need.

(Joe, I assume yours does too, but I know nothing about VBA, so couldn't comment

Assume you have a list of the 3 items that you sell :

Car (code CAR1)
Train (code TRAIN1)
Plane (code PLANE1)

And as you say, you have 3 different prices for each of them :

Car : $30,000 / $31,000 / $32,000
Train : $1,000,000 / $1,200,000 / $1,300,000
Plane : $450,000 / $500,000 / $999,999

I'm going to *assume* you have a problem with the prices bit of your scenario, not the description bit...... linking the description to the code is an easy VLOOKUP..... if I'm wrong, I apologise, repost and it will easily be solved. It also doesn't affect the prices problem, so I'll continue :

You have 3 different prices for each code. So all you do is list those prices out somewhere WITH THE RESPECTIVE CODES:

CAR1
31,000
32,000
33,000

TRAIN1
1,000,000
1,200,000
1,300,000

PLANE1
450,000
500,000
999,999

now highlight the 3-celled range for CAR1
insert
name
define
names in workbook should be "CAR1"
refers to should be your highlighted range (with $$ signs to anchor it)
add
okay

now highlight the 3-celled range for TRAIN1
insert
name
define
names in workbook should be "TRAIN1"
refers to should be your highlighted range (with $$ signs to anchor it)
add
okay


now highlight the 3-celled range for PLANE1
insert
name
define
names in workbook should be "PLANE1"
refers to should be your highlighted range (with $$ signs to anchor it)
add
okay

you should now have a set of named ranges called "CAR1", "TRAIN1" and "PLANE1"

check this as follows :

edit
goto
reference : =CAR1
okay

it should highlight the 3 cells of prices relating to the sale of your car

repeat for TRAIN1 and PLANE1

now, back to your dropdown lists.....

if your original selection of codes was in, say, C10, wether typed in manually or selected from a dropdown, it should contain one of the following :

CAR1
TRAIN1
PLANE1

Cell D10 will contain the description of that item (ie Car, Train, Plane)

in the next cell, E10, we want to validate it with data based on C10, so in E10 :

data
validation
settings
allow = list
source = =INDIRECT(C10)
okay

[stevebausch]

If I understand you correctly.....

A drop-down menu (actually, in my case it was a listbox) _Change event will determine what choices will be seen in the adjacent menu.

I suggest keeping all the hidden data in arrays of control, which in my case were Add-ed as Listboxes. These controls are Visible=False; never seen.

You will have a "proxy" control on the form, for each array. By having the proxy, you get to keep Events, since Events are not possible on array of Control. The Events allow you to have cascade of menus, each triggered by any change of the previous menu. Yes, there is a bit of shuffling of data, but I have done it, and it worked wonderfully.
Just be careful of endless _Change loops; you may need to set Application.EventsEnabled = False while doing your magic, then rest to True upon exit.

Have I lost you yet? I hope not. If you need more info, just ask.

Steve