Hi
If so here I post the website content here and attach the sample for your
reference.
NOTE: the content below is quoted from the website
http://www.xldynamic.com/source/xld.Dropdowns.html
The best Excel learning site on the web Another great site with lots of
downloadable material This one has a different style And now back to the
original style after our little excursion with another xlDynamic.com
Excel & VBA Tips
General | Using Excel | Worksheet Formulae | VBA Development | Tools &
Utilities | Miscellaneous
Home Page
Sitemap
Contact xld
Contributions
Terms of Use
Disclaimer
Menu Page
Techniques
Coloured Cells
Conditional Formatting
Rounding In Excel
Last Value In Range
Utilities
Reference
Special Characters
Excel Names
Menu Page
Techniques
RANK
Utilities
Reference
SUMPRODUCT
Menu Page
Techniques
Automation?
Comon Dialog?
Conditional Deleting?
Test File Open
Utilities
Dependent Dropdowns
Document Property
ReferenceMenu Page
Techniques
Utilities
CFPlus
Dependent Dropdowns
League Table
QDE
ReferenceMenu Page
Reference
FAQs
Book List
Downloads
Glossary
Web Sites
Excel Sites?
Back Issues
EEE back issues?
ADO/ADOX
Read Excel
Write Excel
Worksheet Names
Early v Late Binding
Win32 APIs
Common Dialog - Overview
CDT - Goto Sheet
CDT - Hide Sheets
CDT - Sort Sheet(s)
FileSystemObject
Conditional Deleting
Conditional Deleting v1
Conditional Deleting v2
Conditional Deleting v3
Chip Pearson
John Walkenbach
Dave McRitchie
Ole Erlandsen
Alan Beban's Array UDFs
Issue 19
Issue 18
Issue 17
Issue 16
Main > VBA Development > Dependent Dropdowns
Dynamic Dependent Dropdowns
This technique shows how to create dynamic dropdown lists in VBA. Dynamic
dropdowns in this context refers to a dynamically changing secondary list,
that changes dependent upon a selection from a primary list. An example is
a primary list of continents, and a secondary list showing the countries
associated with the selected continent.
Below is an example of dynamic dropdowns, to demonstrate the technique, As
this is a web page, this is a JavaScript solution, but the VBA solution
works in a similar way.
Continent Country City
America Europe Africa Far East Indian Sub-Continent [nothing selected]
[nothing selected]
This page demonstrates how such a technique might be implemented. In
addition, at the end of the article, you can download an example workbook
that provides all of the code and data discussed here. The workbook is a
fully working example that can be used for your own projects, all that is
needed is to change the tables/lists on the 'Data' worksheet to your own
data.
In this discussion two separate implementations are provided, one using
comboboxes as the dropdowns, and one using Data Validation lists as the
dropdowns.The example workbook shows both implementations.
The article is segmented into the following sections:
Sample Data
Naming The Ranges
Combobox Version
Data Validation Version
Adding Further Dropsdowns
Download
Sample Data
The first step is to create a set of tables/lists similar to the table
below. This is a truncated version of the tables in the example workbook.
Figure 1.
Naming The Ranges
These tables are used in the code to poulate the dropdowns. The values in
row 1, from column B on, are the groups that are used to populate the
primary dropdown. This set of values is given a workbook name of
'List1Values'. The values in row 2 to row n for each of the populated
columns are defined as workbook named ranges which are used by the VBA
code
to populate the secondary dropdown, based upon the value chosen in the
primary dropdown. Each column of values is given a separate workbook named
range, and because they are used in the VBA code, these secondary lists
have to be named in a very structured manner. In this code, I am using
named ranges of 'List2_1', 'List2_2', etc.
In practice, the VBA code should take care of creating the named ranges,
simply by examining the data, and defining the named ranges accordingly.
This allows further secondary lists to be added without any other user
action, and/or extra items or changes in any of the secondary lists. The
code that creates the named ranges is shown here:-
Public Const kApp As String = "Dynamic Dropdowns"
Public Const kList1Hnd As String = "List1Values"
Public Const kList2Hnd As String = "List2_"
'---------------------------------------------------------------------
Public Sub pzLoadList2Lists()
'---------------------------------------------------------------------
Dim oWsData As Worksheet
Dim cRows As Long, cCols As Long, i As Long, j As Long
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo load_exit
Set oWsData = data
With oWsData
'create dynamic range names for List1 and List2 lists
cCols = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To cCols
cRows = .Cells(Rows.Count, i).End(xlUp).Row
ThisWorkbook.Names.Add Name:=kList2Hnd & i - 1, _
RefersToR1C1:="='" & data.Name & _
"'!R2C" & i & ":R" & cRows & "C" & i
Next i
End With
ThisWorkbook.Names.Add Name:=kList1Hnd, _
RefersToR1C1:="='" & data.Name & "'!R1C2:R1C" &
cCols
load_exit:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
This code should be called from the Workbook_Open event, in order to
initially populate the comboboxes upon opening the workbook, and also
whenever any change is made to the data (in that worksheet's
Worksheet_Change event).
The other item that needs to be setup is the codename for the worksheets.
This is necessary as the worksheet is referred to in the VBA code by its
codename, in case the worksheet name is changed by a user. This code
assumes that that 'Data' worksheet has a codename of 'data', that the
combobox example worksheet has a codename of 'combo', and that the Data
Validation example worksheet has a codename of 'dv'.
Combobox Version
So, how does it all work? As mentioned above, the named ranges are defined
by VBA code called whenever the workbok is opened, and re-defined whenever
data on that worksheet changes. Another worksheet is required that will
hold the dropdowns that will have the dynamic functionality. In my
example,
I called this worksheet 'Combo Example', and added two control toolbox
comboboxes to it, which are named as 'cboPrimary' and 'cboSecondary'.
The secondary combobox is loaded as part of the primary combobox change
event, that is, selecting a value from the primary combobox triggers the
following code:
'---------------------------------------------------------------------
Private Sub cboPrimary_Change()
'---------------------------------------------------------------------
Dim idx As Long
Dim iTargetCol As Long
Dim oFoundCell As Range
With data.Range("List1Values")
Set oFoundCell = .Find(what:=cboPrimary.Value, _
LookIn:=xlValues)
If oFoundCell Is Nothing Then
MsgBox "Critical error", vbCritical, "(xld) Dynamic DropDowns"
Exit Sub
End If
End With
'load the List2 dropdown and set the default to item 1
iTargetCol = oFoundCell.Column - 1
pzPopulateList2 iTargetCol
End Sub
In this code, the range 'List1Values' is searched for the value selected
in the primary combobox. This search returns the ordinal number within
those values, which in turn is passed as the secondary list range index to
the pzPopulateList2 procedure to re-populate the secondary combobox.
The final piece of the jigsaw for this combobox solution is the code for
populating the primary and secondary comboboxes. The following code shows
how this is achieved.
'---------------------------------------------------------------------
Public Function pzPopulateList1()
'---------------------------------------------------------------------
Dim i As Long
Application.EnableEvents = False
On Error GoTo pl1_exit
With combo.cboPrimary
.Clear
For i = 2 To Range(kList1Hnd).Count + 1
.AddItem data.Cells(1, i).Value
Next i
Application.EnableEvents = True
.ListIndex = 0
End With
pl1_exit:
Application.EnableEvents = True
End Function
'---------------------------------------------------------------------
Public Function pzPopulateList2(idx As Long)
'---------------------------------------------------------------------
Dim i As Long
Dim sList As String
Application.EnableEvents = False
On Error GoTo pl2_exit
sList = "=" & kList2Hnd & CStr(idx)
With combo.cboSecondary
.Clear
For i = 1 To Range(sList).Count
.AddItem Range(sList).Cells(i, 1).Value
Next i
Application.EnableEvents = True
.ListIndex = 0
End With
pl2_exit:
Application.EnableEvents = True
End Function
The procedure pzPopulateList1 takes each of the items in the named range
'List1Values', and loads them into the primary combobox.
The pzPopulateList2 procedure is just a little more complex, in that it is
passed an index argument. This index refers to the ordinal number of the
item selected in the primary combobox, and is used to determine which
secondary list will be loaded into the secondary combobox. Using the named
range structure mentioned earlier, this index is used to identify which
named range is loaded into the secondary combobox.
Data Validation Version
The Data Validation version uses very similar techniques to the Combobox
version , but the population code is specific to Data validation. In this
implementation, two cells are used that utilise the Data Validation
functionality, specifically Data Validation using custom lists. These two
cells cover the primary data list, and the secondary, related, data list.
This implementation utilises the built-in Excel Data Validation
functionality, and simply sets the function up with the correct lists.
Again, the named ranges are used in the VBA code to facilitate the
loading,
for two reasons. Firstly, I find named ranges very flexible and powerful,
and secondly and most importantly, Data Validation can only refer to
ranges
on another worksheet if named ranges are used. The same VBA code that
automatically names the ranges in the combobox implementation is used for
the Data Validation implementation.
As already mentioned, the Data Validation implementation works in a
similar
manner to the combobox implementation in that both Data Validation cells
need to be initially populated when the workbook is opened. This is the
code in the Workbook_Open event that performs that function
'---------------------------------------------------------------------
Private Sub Workbook_Open()
'---------------------------------------------------------------------
Dim cell As Range
pzLoadList2Lists
Application.DisplayAlerts = False
'this poulates the Data Validation lists
Set cell = dv.Range(kList1)
fzCreateValidationList1 cell
fzCreateValidationList2 cell.Offset(1, 0), 1, cell
Application.DisplayAlerts = True
End Sub
The primary and secondary Data Validation cells also need to be
re-populated if there is any change to the data tables/lists on the 'Data'
worksheet. Again, this is achieved using Worksheet_Change event code,
which
is shown below
'---------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'---------------------------------------------------------------------
Dim oFoundCell As Range
Dim iTargetCol As Long
If Not Intersect(Range(kList1), Target) Is Nothing Then
If Target.Count = 1 Then
With data.Range(kList1Hnd)
Set oFoundCell = .Find(what:=Target.Value, _
LookIn:=xlValues)
If oFoundCell Is Nothing Then
MsgBox "Critical error"
Exit Sub
End If
End With
'load the List2 dropdown and set the default to item 1
iTargetCol = oFoundCell.Column - 1
fzCreateValidationList2 Target.Offset(1, 0), iTargetCol, Target
Target.Offset(1, 0).Value = data.Range(kList2Hnd &
iTargetCol).Value
End If
End If
End Sub
And finally, here is the code that actually sets up the Data Validation
in the two cells. The approach taken here is to delete any exising data
validatioon already defined to that cell, and then re-create anew based
upon the actual data in the tables/lists. The primary Data Validation cell
is quite simple as it just assigns the named range 'List1Values' to the
Data Validation set up in that cell. The secondary Data Validation is just
a little more complex as it is passed the primary list index as an
arguement which is used to construct the name of the range to be assigned,
based upon the value 'List2_' and the index, and the error message
reflects
the value selected from the primary Data Validation cell.
'---------------------------------------------------------------------
Public Function fzCreateValidationList1(Target As Range)
'---------------------------------------------------------------------
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertWarning, _
Formula1:="=" & kList1Hnd
.InCellDropdown = True
.InputTitle = kApp
.ErrorTitle = kApp & " - Error"
.InputMessage = ""
.ErrorMessage = "This is not a valid Value"
End With
End Function
'---------------------------------------------------------------------
Public Function fzCreateValidationList2(Target As Range, _
idx As Long, _
source As Range)
'---------------------------------------------------------------------
With Target.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertWarning, _
Formula1:="=" & kList2Hnd & idx
.InCellDropdown = True
.InputTitle = kApp
.ErrorTitle = kApp & " - Error"
.InputMessage = ""
.ErrorMessage = "This is not a valid Value for " & source.Value
End With
End Function
There is just one other thing to note regarding Data Validation. The
example given above works perfectly well in Excell 2000 and on, but if you
have Excel 97, you will find that changing the value in the primary cell
does not cause an update of the list associated with the secondary cell.
This is because a change to a data validation cell does not fire the
Worksheet_Change event in Excel 97. The solution to this is to use another
event. I have used the Worksheet_Calculate event, and to trigger it I have
referenced the primary Data Validation cell in an other cell (in which I
have set the font colour to white, to hide it), with a simple =List1.
Thus,
when a change is made to the primary Data Validation cell, this changes
the
linked cell, which in turn triggers the Worksheet_Calculate event. The
example workbook uses this technique, so it works with Excel97 on.
Ading Further Dropdowns
The code presented is fully-inclusive, and can handle any number of
dropdowns (although the more you have, the slower it is bound to become,
and the lists could become unmanageable). If you want to add further
dropdowns, this is an outline of the process:
Open the workbook with macros disabled
Add a fourth control toolbox combobox and name it cboList4
On the data sheet add a fourth column labelled List4
Add data in column 4, inserting rows for repetition of Lists3, 2 and 1
Close the workbook
Open it with macros enabled
Try it with a few values at the top to start with, it is straight-forward.
Download
2179
Click here to download an example workbook.
----------------------------------------------------------------------------
----
Copyright ?2000-2004 xlDynamic.com
Page last updated: 22nd December 2003
Found an error, a bug or just want to
comment on this page, please tell us
Copyright ?2000-2004 xlDynamic.com Home
24742
Best regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! -
www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.
