Simplifying cascading validation with complex lists
Validation lists are often used in Excel to improve data entry. By
defining a named range and using that as the source for a validation
list, you can store your lists in one worksheet and use them throughout
the workbook. The problem arises when you want the second list to filter
based on your first selection, and so on. You can create named ranges
whose names are exactly the same as the items in the parent lists, and
then refer to those ranges —
here is a good
example from another site showing how to do that — but there are two major shortcomings.
- Items with spaces will have those spaces replaced with
underscores when you create range names. You will need to work
around this to correctly reference the second and subsequent lists. - Because each unique item in the cascade needs its own named
list, the number of lists can quickly get out of hand and become a maintenance
nightmare.
I got caught by the second issue once, and found myself building
about 120 lists to maintain a data entry form. So, I went looking for an
alternative and built the system described in this article. It is an
extension of a tip that I published a while ago, and uses ADO to create
the lists on the fly. The result is that you now maintain one large
table with all of the possible combinations, which is much simpler to
administer.
The sample file contains a table of postcodes in New South Wales,
Australia; over 5000 unique entries. As you make a selection, event code
on the data entry sheet calls a subroutine to build the dependent list.
There is a button on the lookup sheet that rebuilds the first lookup
list if you alter the main table. In all, only four lists are used and
three of them are maintained for you.
- You can download the sample file
here.
It is an .xls file and has been tested in Excel 2003 and 2007. If
you save the file as an .xlsm (for 2007) it will work without
modification.
The named ranges
Each of the validated data entry cells has a name; so does the list
that each one references. The list names are
dynamic so that they don’t need
to be redefined. You will need one list with a named range for each
drop-down.
Triggering the code
General use
The code is triggered from the Worksheet_Change event of the
active worksheet. Right-click the worksheet tab, View > Code, and
paste this into the module:
Private Sub Worksheet_Change(ByVal Target As Range)
'Stop the code from firing unless the desired cell has changed
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Address
Case Is = Range("BSPName").Address
Call ADO_Self_Excel("BSPName", "BSP_Name", "Locality", 2)
Case Is = Range("Locality").Address
Call ADO_Self_Excel("Locality", "Locality", "Post_Code", 4)
Case Else
Exit Sub
End Select
End Sub
If either range BSPName or Locality updates, the main routine is
called. Any other changing cells are ignored.
Updating the first list if the main data table changes
If you alter the main table (in this example, the PostCodes
sheet) you will need to re-create the first list in the cascade.
There is a button on the Lookups sheet that triggers the following
code (in the same module as the main routine) to do the job.
Sub RefreshFirstList()
Call ADO_Self_Excel("", "BSP_Name", "BSP_Name", 6)
End Sub
ADO_Self_Excel takes four parameters.
- The first is the named cell corresponding to the validation
list. If you enter a name here, the downstream list will be
filtered. If you use null quotes (“”) the output will not be
filtered. This is used to create a set of unique names for the
first validation list. - The second parameter is the heading of column that will be
filtered. - The third parameter is usually the adjacent column heading.
Values from this column will be transferred to the Lookup sheet. - The fourth parameter is the number of the column where the
output list will be written.
As an example, Call ADO_Self_Excel(“BSPName”,”BSP_Name”,”Locality”,2)
uses the value in the named cell BSPName to filter the column whose
heading is BSP_Name and transfer the filtered contents of the
Locality column to column 2 of Lookups.
The main routine
The main code goes into a new module, and has two routines. The
first, ADO_Self_Excel, does the work of building the lists. The second,
RefreshFirstlist, rebuilds the first validation list if the main data
table is changed. In the sample file the code is triggered by a button
placed on the Lookups sheet. You will need to
set a
reference to the Microsoft ActiveX Data Objects 2.8 Library to use
this code.
The first step is to define the variables and define the file
path to the source file. As we are pulling data from a sheet in the
same workbook, we just use ActiveWorkbook.FullName as the source
path.
Sub ADO_Self_Excel(sCallerRange As String, sSourceCol As String, _
sDestField As String, iDestCol As Integer)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
Dim sPath As String
Dim MyConn
Dim sFilter As String
sPath = ActiveWorkbook.FullName
Next, the filter is defined. You have the option of using a range
name for sCallerRange (the normal situation when you build the
dependent lists), or a null quote “” (when defining the first list;
see the code for RefreshFirstList further down the page). If you use
a null quote the filter is made into a null quote, otherwise a wild
card character (%) is appended to the range name.
'Define the filter and the SQL statement that extracts the names.
'The validation lists are all on the Menu worksheet; change name to suit.
'Use % as the wild card character in ADO, not *
If sCallerRange = "" Then
sFilter = ""
Else
sFilter = (Sheets("Menu").Range(sCallerRange).Value) & "%"
End If
The SQL that defines the recordset is built next. The WHERE
(Criteria) clause is optional, and the code includes it when you
pass a range name as the first parameter. By using SELECT DISTINCT
we get a unique list; the ORDER BY clause sorts the list.
'PostCodes is the SOURCE sheet. Change name to suit your setup.
sSQL = "SELECT DISTINCT " & sDestField & " FROM [PostCodes$]"
If sFilter <> "" Then
sSQL = sSQL & " WHERE " & sSourceCol & " Like '" & sFilter & "'"
End If
sSQL = sSQL & " ORDER BY " & sDestField
With the path to the file and the SQL defined, it’s time to
create the ADO connection and open the recordset.
'Establish connection to the same file
'When connecting to Excel instead of a database, you need to define
'the extended properties as Excel 8.0 (The first Excel version to use ADO)
MyConn = sPath
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open MyConn
End With
'Define a recordset based on the SQL statement
'The Options parameter is set to adCmdText for a query
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, _
ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, _
Options:=adCmdText
Finally, the list is transferred to its destination. Any existing
values in the list are cleared first, then the data is transferred
and the recordset and connection are closed.
Application.ScreenUpdating = False
'Delete existing data on the destination sheet, then
'transfer the results of the latest filter, starting at Row 2.
'When done, clean up references to avoid memory leaks.
With Sheets("Lookups") 'Lookups is the DESTINATION sheet. Change to suit.
.Cells(1, iDestCol).CurrentRegion.Offset(1, 0).Clear
.Cells(2, iDestCol).CopyFromRecordset rst
End With
rst.Close
cnn.Close
Application.ScreenUpdating = True
End Sub
Things to watch
Make sure that your column headings in the main data sheet don’t
have any spaces. Because we are using ADO they will be regarded as
field names, and field names with spaces are best avoided.
You will also need to change the names of the three worksheets —
the positions are flagged in the code. If you don’t do this you will
get a “Subscript out of range” error at run time.
Wrapping up
Validation lists in Excel are powerful, but if the lists are
complex you will find that building and maintaining the dependent
lists is hard work. In that case, I hope that you find this article
useful.
