Excel Data Validation–Create Dynamic Dependent Lists (VBA)
July 29, 2011Posted by on
I have stopped updating this blog. This link can also be found in my Website.
There are times when you need to create dependent lists from a dynamic range. The data for the list can come from, say an SQL Database or even a website.
Lets say we came across a list on a website which is similar to this.
Now we want to create two data validation list, where the first list shows the name of the Country and the second shows the names of the Cities. Also what we would like is that if I select “India” from the first list then the second list should automatically display the cities from India only. So how do we achieve this?
The below code will help you create dependent lists just by simply pasting data in the Source Columns. For the sake of simplicity we will copy and paste the above list in Column A and Column B of an Excel Sheet, say “Sheet1”. However before we do that, we have to paste the below code in the Sheet Code Area. The sheet code area can be accessed by pressing Alt+F11 from the main worksheet. If you want to access the Visual Basic Editor from the menu then do this.
Excel 2003 : Click on the Tools menu and then click on Macros
Excel 2007 : Click on the Developer Tab and then click on the “Visual Basic” as shown in the picture below
Excel 2010 : Click on the Developer Tab and then click on the “Visual Basic” as shown in the picture below
Excel 2011 (MAC) : Click on the Developer Tab and then click on the “Visual Basic” as shown in the picture below
Once you have accessed the Visual basic Editor, the screen will look like this (This screen is from 2007. The other versions will have a similar screen).
Double click on the Excel object Sheet1 (Sheet1). The moment you double click, the code area on the right hand side will open up for you. Simply paste the code there and you are ready
I have commented the code so that it is easier to understand.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long, LastRow As Long, n As Long Dim MyCol As Collection Dim SearchString As String, TempList As String Application.EnableEvents = False On Error GoTo Whoa '~~> Find LastRow in Col A LastRow = Range("A" & Rows.Count).End(xlUp).Row If Not Intersect(Target, Columns(1)) Is Nothing Then Set MyCol = New Collection '~~> Get the data from Col A into a collection For i = 1 To LastRow If Len(Trim(Range("A" & i).Value)) <> 0 Then On Error Resume Next MyCol.Add CStr(Range("A" & i).Value), CStr(Range("A" & i).Value) On Error GoTo 0 End If Next i '~~> Create a list for the DV List For n = 1 To MyCol.Count TempList = TempList & "," & MyCol(n) Next TempList = Mid(TempList, 2) Range("D1").ClearContents: Range("D1").Validation.Delete '~~> Create the DV List If Len(Trim(TempList)) <> 0 Then With Range("D1").Validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=TempList .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End If '~~> Capturing change in cell D1 ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then SearchString = Range("D1").Value TempList = FindRange(Range("A1:A" & LastRow), SearchString) Range("E1").ClearContents: Range("E1").Validation.Delete If Len(Trim(TempList)) <> 0 Then '~~> Create the DV List With Range("E1").Validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=TempList .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End If End If LetsContinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub '~~> Function required to find the list from Col B Function FindRange(FirstRange As Range, StrSearch As String) As String Dim aCell As Range, bCell As Range, oRange As Range Dim ExitLoop As Boolean Dim strTemp As String Set aCell = FirstRange.Find(what:=StrSearch, LookIn:=xlValues, _ lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) ExitLoop = False If Not aCell Is Nothing Then Set bCell = aCell strTemp = strTemp & "," & aCell.Offset(, 1).Value Do While ExitLoop = False Set aCell = FirstRange.FindNext(After:=aCell) If Not aCell Is Nothing Then If aCell.Address = bCell.Address Then Exit Do strTemp = strTemp & "," & aCell.Offset(, 1).Value Else ExitLoop = True End If Loop FindRange = Mid(strTemp, 2) End If End Function
Once the code is pasted, you can directly paste the list in Column A and Column B and the Data Validation List will be created in D1. Now when you select an option in D1, the Dependent Data Validation List will automatically be created in cell E1.
Attached is a video which shows the entire process. In the video, If you notice, I changed one of the city and it reflected in the Data Validation List.
Dynamic Dependent Lists
The above code can be amended to create more than 1 Dependent Data Validation List. You can also amend it so that the Column A and Column B List can be copied to a different Worksheet and you can base your Data Validation Lists on them. This is useful, if you do not want the user to see your list.