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.
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.
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.
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.
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.
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.
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 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
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.
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.