How to Make a Dynamic Data Validation List Using VBA in Excel

In dynamic data validation, when you add a new entry to the source list, the entry is automatically added to the data validation list. It is a very efficient technique to add new data. To make a data validation list dynamic you need to insert a code in Microsoft Visual Basic Application (VBA). In this article, I’ll show you how you can create a dynamic data validation list in Excel using VBA in 3 easy steps.

Suppose, you have the following dataset where you want to create a data validation list to enter the States name for each Branch Name. You will use the States List in column F as the source list for data validation.

dataset


Download Practice Workbook


3 Steps to Create Dynamic Data Validation List Using VBA in Excel

1. Create a Dynamic Name Range

First, you have to create a dynamic name range for the States List. To do that,

➤ Go to the Formulas tab and select Name Manager.

name manager

As a result, a Name Manager window will appear.

➤ Click on New in the Name Manager window.

name manager

Now, a new window named New Name will appear.

➤ Type a name for the list in the Name box.

For this demonstration, I’ve inserted the name States.

After that,

➤ Insert the following formula in the Refers to box,

=OFFSET('Data Validation'!$F$5,0,0,COUNTA('Data Validation'!$F:$F)-1)

The formula will create a dynamic cell reference for the name States. If you insert any new entry in column F, the name States will be automatically assigned to the entry.

➤ Click on OK.

new name

As a result, the name States will be added to the Name Manager.

➤ Click on Close in the Name Manager window.

excel vba dynamic data validation list

Read More: How to Create Dynamic Drop Down List Using Excel OFFSET (3 Ways)


2. Add Data Validation

In this step, you need to add data validation to the cells of column B. To do that,

➤ Select the cells where you want to add data validation.

➤ Go to Data > Data Tools > Data Validation > Data Validation.

data validation

As a result, the Data Validation window will appear. Now,

From the Allow box, select List, and the Source box select the list from column F.

Click on OK.

data validation

As a result, data validation will be added to the selected cells. Now, if you click on the downward arrow beside the cells of column B a dropdown list will appear. Here, you will see the names of the states of the list of column F.

excel vba dynamic data validation list

Now, if you enter a new entry in column F (Here I’ve entered Indiana) and click on the downward arrows beside the cells of column B, you will see that the new entry isn’t showing in the dropdown list.

excel vba dynamic data validation list

This is happening because your data validation list is not dynamic. To automatically update of new entry you will need to make the list dynamic. You can do that with a simple VBA code which I’ll show you in the next step.


3. Make the Data Validation List Dynamic with VBA

In this section, I’ll add a VBA code to make the validation dynamic. Let’s start the procedure.

➤ Right-click on the sheet name

As a result, a menu will appear.

➤ Select View Code from this menu.

excel vba dynamic data validation list

As a result, the Code window of the VBA will be opened.

➤ Insert the following code in this window,

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ListRow As Single
Dim CString As String
Dim Value As Variant
If Not Intersect(Target, Range("$C:$D")) Is Nothing _
Or Not Intersect(Target, Range("F:F")) Is Nothing Then
    ListRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
    For Each Value In Range("States")
        CString = CString & "," & Value
    Next Value
    With ActiveSheet.Range("B5:B" & ListRow).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=CString
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If

End Sub

The code will change the worksheet in a way that when you will insert a new entry in column F,  and if there is data in columns C and D of a row, the new entry will be automatically updated in the data validation list of column B of that row.

excel vba dynamic data validation list

After inserting the code,

➤ Close the VBA window.

Now, if click on the downward arrows beside the cells of column B, you will see that the new entry Indiana is showing in the dropdown list at this time.

excel vba dynamic data validation list

If you add another entry in column F (Nevada), it will be automatically added to the data validation list.

excel vba dynamic data validation list

Now, you insert the data from column F in column B by selecting from the dropdown list. If you need to insert new data, you can add that in column F. As a result, the data will automatically appear in the dropdown list. For Example, in cell B15, you need to insert the state name Kentucky which is not listed in column F.

➤ Insert the name Kentucky in column F.

As a result, you will see the name in the dropdown list when you will click on the downward arrow beside cell B15.

excel vba dynamic data validation list

If you want to remove any names from the dropdown list,

➤ Delete the name from column F.

The name will be automatically removed from the dropdown list.

excel vba dynamic data validation list

Read More: How to Create a Dynamic Top 10 List in Excel (8 Methods)


Conclusion

If you don’t insert the VBA code, your data won’t be automatically updated in the dropdown list. The VBA code made the data validation list dynamic. In this article, I’ve discussed how you can create a dynamic data validation list in Excel using VBA. I hope now you’ll be able to create dynamic data validation list according to your needs. If you have any confusion, please feel free to leave a comment.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

1 Comment
  1. im trying to apply this process to a worksheet with multiple sheets:

    I have an information template sheet that has my lists for the drop downs and would like to add the drop down to the schedule sheet…

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim st As Worksheet
    Set st = ThisWorkbook.Sheets(“Schedule Template”)
    Dim ListRow As Single
    Dim CString As String
    Dim Value As Variant
    If Not Intersect(Target, st.Range(“$B:$C”)) Is Nothing _
    Or Not Intersect(Target, st.Range(“e:e”)) Is Nothing Then
    ListRow = st.Range(“B” & Rows.Count).End(xlUp).Row
    For Each Value In Range(“ClassLocations”)
    CString = CString & “,” & Value
    Next Value
    With st.Range(“E14:E” & ListRow).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=CString
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = “”
    .ErrorTitle = “”
    .InputMessage = “”
    .ErrorMessage = “”
    .ShowInput = True
    .ShowError = True
    End With
    End If

    End Sub

    this is throwing errors

Leave a reply

ExcelDemy
Logo