Dependent List Box (Non Data Validation)

B

BrianExcel

Well-known Member

Joined

Apr 21, 2010

Messages

975

• Mar 15, 2011

• #2

What exactly are you trying to do with the listbox?

 

Upvote 0

J

jaysunice

New Member

Joined

Jan 13, 2011

Messages

12

• Mar 16, 2011

• #3

Say for example, I have:

Fruits: Apple, Pear, Orange
Vegetables: Carrot, Celery, Potato, Spinach
Dairy: Milk, Cheese, Cream, Butter

I want ListBox 1 to drop-down and display (Fruits, Vegetables, Dairy) -- then, ListBox 2 will dependently display the appropriate list (Apple, Pear, Orange OR Carrot, Celery, Potato, Spinach, OR Milk, Cheese, Cream, Butter).

 

Upvote 0

B

BrianExcel

Well-known Member

Joined

Apr 21, 2010

Messages

975

• Mar 16, 2011

• #4

I uploaded a quick crude version onto box.net for you to look at. It's easier to do what you are asking with a Combo Box. Check it out. I don't know how your data is laid out but this is the easiest way I know to do it.

http://www.box.net/shared/c39x8tm477

Hope this helps.

 

Upvote 0

J

jaysunice

New Member

Joined

Jan 13, 2011

Messages

12

• Mar 16, 2011

• #5

Thanks BrianExcel!

I was actually trying to do this with Forms Controls rather than ActiveX, but this will do the job.

Nice clean code too -- definitely a worthwhile example for everyone to put in their back pocket.

 

Upvote 0

B

BrianExcel

Well-known Member

Joined

Apr 21, 2010

Messages

975

• Mar 16, 2011

• #6

No problem.
Actually, doing it on a form is just about as easy. In the code, instead of referencing

Code:

Sheets("Sheet1").listbox(or .combobox, etc)

You would reference

Code:

Userform1(or whatever you name it here).listbox(or.combobox, etc)

Glad I could help!

 

Upvote 0

N

ndendrinos

Well-known Member

Joined

Jan 17, 2003

Messages

1,694

• Mar 16, 2011

• #7

Downloaded BrianExcel's file but could not read it as I use Excel XP
Out of curiosity maybe you could let me know if you can work with this file also?
http://www.box.net/shared/oouv3icpyr
It consist of a user form with cascading list boxes

 

Upvote 0

B

BrianExcel

Well-known Member

Joined

Apr 21, 2010

Messages

975

• Mar 16, 2011

• #8

Nicely done ndendrinos. That definitely works too.

 

Upvote 0

N

ndendrinos

Well-known Member

Joined

Jan 17, 2003

Messages

1,694

• Mar 16, 2011

• #9

Hey I'm just glad it works in Excel 2010. I'm sure your solution is better.
have a good day

 

Upvote 0