Cascading combo box and dynamic ranges

**penfold1992** · 08-21-2013, 07:06 AM

I want to create a cascading combo box from some values that I have, ill explain how I approached it.

Cascading combo box is a combobox which values are obtained depending on the answer of a previous combo box.
So I set up a little table like so:
Categories Car Train Bike
Car Mercades First Honda
Train Ford Western Ducati
Bike Skoda Red Suzuki

and the combo boxes would be set aside from that.
I collected the data in Named Ranges (A Range for Categories, A Range for Car, A Range for Train...)

The first data validation box contains = Categories
the second data validation box contains =INDIRECT($C$2) (Where C2 was the range of the first validation box)

Once this was working, I decided to change the formula for the named ranges to allow me to add more values.
The formula was: =$F3$F8
I changed it to: =OFFSET($F$3,0,0,COUNTA($F3:$F96),1)

I expected this to work however, it just doesnt.... when clicking on the drop down arrow for the validation box, nothing happens. No box or values or dropdown.

anyone know why? or how I can achieve this?

**blue.chio** · 08-24-2013, 04:48 AM

Hi,
perhaps something like this
let's say you have in col A "categories", Col B "car", Col C "train", Col D "bike"
in a userform add 2 comboboxes then add this piece of code for combobox1:

Private Sub ComboBox1_Change()
With Sheet1
Select Case Me.ComboBox1.ListIndex
Case 0
    Me.ComboBox2.RowSource = Sheet1.Range("b2", Range("b65536").End(xlUp)).Address 'car
Case 1
    Me.ComboBox2.RowSource = Sheet1.Range("c2", Range("c65536").End(xlUp)).Address 'train
Case 2
    Me.ComboBox2.RowSource = Sheet1.Range("d2", Range("d65536").End(xlUp)).Address 'bike

End Select
ComboBox2.ListIndex = 0
End With
End Sub

and this one for userform

Private Sub UserForm_Initialize()
Me.ComboBox1.RowSource = Sheet1.Range("a2", Range("a65536").End(xlUp)).Address 'categories
End Sub

or
http://www.contextures.com/xlDataVal02.html

**Ursul** · 08-24-2013, 06:33 AM

Hi Penfold1991,

Attached is an example using Drop-down Data Validation in place of the Combo.

DATA VALIDATION - NOT COMBO.xlsx

Cascading combo box and dynamic ranges

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Cascading combo box and dynamic ranges

Re: Cascading combo box and dynamic ranges

Re: Cascading combo box and dynamic ranges

Thread Information

Users Browsing this Thread

Similar Threads

Workaround for Cascading Data Validation with Dynamic named ranges!!!!!

[SOLVED] Adding dynamic named ranges to combo boxes in userform

[SOLVED] Cascading Comboboxes With Multiple Dynamic Named Ranges?

HELP! Excel Data Validation Combo box using dynamic ranges

Cascading dropdown list with dynamic ranges

Bookmarks

Bookmarks

Posting Permissions