use of category / subcategory pattern with excel combo boxes - please help!

[Alex]

i have two excel columns that follow the parent/child pattern (ie.
category/subcategory)

they look like something like this:


row a row b
-------------------------------------------------------------------------------
category 1 subcategory 1
category 1 subcategory 2
category 1 subcategory 3
category 1 subcategory 4
category 1 subcategory 5
category 2 subcategory 6
category 2 subcategory 7
category 2 subcategory 8
category 2 subcategory 9
category 2 subcategory 10
category 3 subcategory 11
category 3 subcategory 12
category 3 subcategory 13
category 3 subcategory 14
category 3 subcategory 15

what I am trying to do is create two combo boxes, where the second box
loads itself with options that depend upon what was selected in the
first combo box.

in my specific example, the first combo box is a list of "column a"

question 1) I figured out how to load the list into the combo box, but
how can I display 3 entries:

ie. make the entries non duplicative, such as seen below:

category 1
category 2
category 3

rather then having 15 choices appear in combo box 1 as now occurs
(which looks like the list below):

category 1
category 1
category 1
category 1
category 1
category 2
category 2
category 2
category 2
category 2
category 3
category 3
category 3
category 3
category 3

question 2) how can I load combo box two with data the depends upon
what was selected in combo box 1

for example:

If category 1 is selected in combo box 1 the combo box 2 should contain
the selections:

subcategory 1
subcategory 2
subcategory 3
subcategory 4
subcategory 5

since the data for category 1 looks like:

row a row b
-------------------------------------------------------------------------------
category 1 subcategory 1
category 1 subcategory 2
category 1 subcategory 3
category 1 subcategory 4
category 1 subcategory 5

similarly

If category 2 is selected in combo box 1 the combo box 2 should contain
the selections:

subcategory 6
subcategory 7
subcategory 8
subcategory 9
subcategory 10

since the data for category 2 looks like:

row a row b
-------------------------------------------------------------------------------
category 2 subcategory 6
category 2 subcategory 7
category 2 subcategory 8
category 2 subcategory 9
category 2 subcategory 10

I would really appreciate any help anyone may be able to give me on
these issues.

Thanks in advance!

-Alex

 

[Jim Cone]

Question 1...
http://j-walk.com/ss/excel/tips/tip47.htm
"Filling a ListBox with Unique Items.

Question 2...
http://support.microsoft.com/kb/213748/en-us
"How to Populate One Listbox Based Upon Another Listbox
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Alex" <[email protected]>
wrote in message
what I am trying to do is create two combo boxes, where the second box
loads itself with options that depend upon what was selected in the
first combo box.

-snip
make the entries non duplicative

-snip-
I would really appreciate any help anyone may be able to give me on
these issues.
Thanks in advance!
-Alex

 

[steve.wessels]

Alex,

I've had a similar challenge in the past; here's a link to a solution
that I use:
http://www.paradigmshift-esg.com/excelexamples/multicategoryselect.xls

A brief description of the approach is that I take the main categories
and put them horizontally. I then list the subcategories under each
category. I use a defined name for the categories, and a defined name
for the sub categories that utilizes the offset function. This makes it
pretty easy to maintain the category lists. There is a little work on
the front end to get the interface to work, but it's quick, dynamic and
requires no coding. I'd provide a full instruction set, but I think
reverse engineering a solution generally works better. One thing to
note, I put the interface and category reference on the same tab, but
you can seperate them onto other tabs without issue.