VBA Dynamic Data Validation Lists
This is the second of 4 tutorials to show how I deal with multiple lists in Microsoft Excel.
Using VBA Dynamic Data Validation
In the previous tutorial we demonstrated how you can make multiple lists dynamic by using the tables feature that is built into Microsoft Excel. In this tutorial we will use some VBA code to change the data validation depending on what is selected from a drop-down list. This is one way that I work with multiple lists dynamically. In this method I use dynamic named ranges and some VBA code activated from the worksheet to get the job done. If you do not wish to use dynamic named ranges then static named ranges will work fine however you will need to update them when any new data is added.
Download the Template for VBA Dynamic Data Validation
I would recommend that you download the template because it will make it easier for you to complete this tutorial. The references below are designed for this template. So save time and heartache – use the template. After you have completed the tutorial then set up your own template and modify the tutorial to suit your own needs.
Video 1. Watch this video for an overview
This is an overview video of the methods that I use to deal with multiple lists in Microsoft Excel.
The first is using tables and named ranges along with the data validation.
(This Tutorial) The second method is to use dynamic named ranges and some VBA code activated from the worksheet.
The third is to use combo boxes and to show how to have the columns displayed and added to the data depending on the column selected.
Lastly we will use listboxes to display all of our data and also allow for column selection before it is added to the data set.
Video 2. Here is the process for a VBA and Data Validation
Process for VBA Dynamic Data Validation as shown in the above video
On the Codes sheet of the template that you downloaded
The first thing we need to do is to add our dynamic named ranges.
If this is new to you then here is a tutorial along with a sample file that you can download that will get you started with dynamic named ranges?
Dynamic Named Range Tutorial
1. Add tables to these dynamic named ranges
2. On the Data Sheet add Data validation
a. Select cell F3
b. On the Ribbon choose Data / Data Validation
c. In Settings select List from Allow
d. Click in the source box and press the F3 key
e. Choose “CodeRanges”
5. On the Data Sheet add this VBA code to the sheet
a. Right click on the sheet tab at the bottom and choose View Code
b. Paste in this VBA code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Range("F3")
If Not Intersect(Target, Range("F3")) Is Nothing Then
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Rng
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
Note: The code above will set the data validation to the named ranges selected from the drop-down list in cell F3. I am using the worksheet change the event in order to run this macro. What that means is that when the value in cell F3 changes the code will run. You will also note that the VBA code here limits the data validation to a set range on your worksheet .You should change this top suit your needs. If you wish this to change simply change of the range in the code and the data validation on the sheet will automatically adjust when the code is run.
Now when you select a Category from cell F3 the data validation will be populated with the list for that category.
Our next tutorial will show how to set the process up with a userform that will enable us to be able to reference all 5 columns of data in the list rather than just one range in the column.