UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Cascading Dropdown Lists (VBA)    


Cascading Dropdown Lists (VBA)

[font = Tahoma]

Cascading Dropdown Lists (VBA)

A cascading dropdown list is a dropdown list where the choices available to the dropdown is limited by a choice made in a previous dropdown list.

There are several methods for accomplishing cascading dropdown lists. The method shown here uses Pivot Tables to provide a unique list of items and VBA to set the filters for those items.

The example presented here is one where the user selects a region, the selected region determines which states can be selected and the selected state determines which cities can be selected.

The regions, states and cities are maintained in an Excel Table, and several pivot tables are built on this data on an auxiliary sheet.

The Data Table looks like the following:

image: pic 1.jpg

Each city is associated with a state and each state is associated with a region. Regions may have multiple states and a state may have multiple cities.

The dropdown lists are managed using the OFFSET command, and for the sake of clarity use helper cells.

First, the highest level pivot table (Region) is created.

image: pic 2.jpg

Next, the State Pivot Table is created.

image: pic 3.jpg

Finally, the City Pivot Table is created.

image: pic 4.jpg

The column totals and row totals for the pivot tables should be turned off.

Now that the pivot tables are in place, the named ranges can be created.

Region_List =OFFSET('Dropdown Lists'!$A$4,0,0,COUNTA('Dropdown Lists'!$A:$A)-1,1) Region Dropdown (Cell J2) has a list type data validation =Region_List.

State_List =OFFSET('Dropdown Lists'!$C$4,0,0,COUNTA('Dropdown Lists'!$C:$C)-2,1) State Dropdown (Cell J4) has a list type data validation = State_List.

City_List =OFFSET('Dropdown Lists'!$F$4,0,0,COUNTA('Dropdown Lists'!$F:$F)-2,1) City Dropdown (Cell J6) has a list type data validation = City List

A Change Event VBA Code is used to set the filters on the pivot table

Private Sub Worksheet_Change(ByVal Target As Range)


Private Sub Worksheet_Change(ByVal Target As Range)

' $J$2 is the Region Dropdown
' $J$4 is the State Dropdown

If Intersect(Target, Range("$J$2,$J$4")) Is Nothing Then Exit Sub

Select Case Target.Address
   Case "$J$2" ' The region dropdown is changed
       Range("$D$1") = Target.Value ' Set the filter on the state pivot table
   Case "$J$4" ' The state dropdown is changed
       Range("$G$1") = Target.Value ' Set the filter on the city pivot table
   Case Else
       MsgBox "An error has occured", vbOKOnly, "Invalid Selection"
End Select

End Sub

If you wish to make the option (All) available again, you can redefine the headers in Cells A3, C3, and F3 to “(All)” and redefine the named ranges for the dropdowns to include them.

The sample apreadsheet is attached here: media: Region_Dropdown2.zip

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 5,209 times.  This page was last modified 15:45, 8 July 2013 by dflak.   Disclaimers