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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

jaysunice

New Member
Joined
Jan 13, 2011
Messages
12
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:

jaysunice

New Member
Joined
Jan 13, 2011
Messages
12
Removing because I spotted my error.
 
Last edited:

dksr84

New Member
Joined
Feb 10, 2015
Messages
5
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/
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,632
If you have a flat file with Brands, Products and Descriptive Categories as headers, this sounds like what AutoFilter would do.
 

dksr84

New Member
Joined
Feb 10, 2015
Messages
5
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.

 

dksr84

New Member
Joined
Feb 10, 2015
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,291
Messages
5,485,973
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top