Multi-Select Dependent List Boxes Possible

jaysunice

New Member
Joined
Jan 13, 2011
Messages
12
Here's the situation. I more or less have a tiny "database" (if you'd even call it that) that contains:
  1. Brands
  2. Products
  3. Descriptive Categories
Each Brand (15) can contain anywhere from 2 to 30 different Products. Each Product, contains the same set of 15 Descriptive Categories.

***

I would like to create a way to have a mult-select list box drop down that would allow me to choose one or more of the Brands. Then, a second dependent list would allow me to choose (one or more) amongst the relevant Products for the associated Brands that were just selected. Finally, a query button that returns the pertinent information in a table format.

***

I know how to create dependent lists using =Indirect() via Data Validation lists, however, this does not translate to Mult-Select List Boxes.

Many Thanks!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Welcome to MrExcel.

Couldn't you use a pivot table?

It might get messy because some of the descriptive categories are lengthy -- but a good consideration. Neverthless, this doesn't get around the dependent-list option. Even if you put the Brand and Product in the Page Fields, they're not context-dependent.

I actually might pull this into Access and make a little mini-application.
 
Last edited:
Upvote 0
Hi I have this same query. to be more particular I come from a pharma field wherein you have multiple trials. The comparator drugs may or maynot be the same in these trials. it is possible that i could have all my trials in one list box and the products associated in a dependent list box. the catch here is I dont want a duplication of the product names if they seem to coincide.

Could some one please help me with this/
 
Upvote 0
With your data as below in sheet 1, and Combobox1 and Listbox1 in sheet2, then:-
Right click sheet2 "Tab" , select "View Code" and Paste the Code below.
NB:- Combox1 will load when you Activate sheet2 and ListBox1 will fill when you select from Combobox1
NB:- Ensure there is a blank line at the end of each "Product Range "
Code:
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("B:B").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]If[/COLOR] Dn(1).Offset(, -1).Value = ComboBox1.Value [COLOR="Navy"]Then[/COLOR]
        ListBox1.List = Dn.Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Activate()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, r [COLOR="Navy"]As[/COLOR] Variant, Sp [COLOR="Navy"]As[/COLOR] Variant, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A:A").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]End[/COLOR] With
ComboBox1.Clear
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    ComboBox1.AddItem Dn.Value
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]




AB
1Trial 1Product 1
2 Product 2
3 Product 3
4 Product 4
5 Product 5
6
7Trial 2Product 6
8 Product 7
9 Product 8
10 Product 9
11
12Trial 3Product 10
13 Product 11
14 Product 12
15 Product 13
16 Product 14
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>
 
Last edited:
Upvote 0
If you have a flat file with Brands, Products and Descriptive Categories as headers, this sounds like what AutoFilter would do.
 
Upvote 0
Thank you so much for the suggestion. I have another report built based on the auto filter option. But in this particular case the headers of the report are going to come in from the database.

Is there a possibility to attach the file here to demonstrate what I am trying to do.

 
Upvote 0
Thank you so much for the suggestion. I have another report built based on the auto filter option. But in this particular case the headers of the report are going to come in from the database.

Is there a possibility to attach the file here to demonstrate what I am trying to do.


I have uploaded the file here: https://app.box.com/s/zqxpy312n9ciy22hvja4z1oamue4hbin

The report page has the userform which would help me create the report. The Mastersheet is the database.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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