Excel VBA Drop Down Lists Using Data Validation

In this article I will explain how you can create drop down lists using data validation.

 

Jump To:

 

You can download the file and code related to this article from the link below:


Creating Data Validation (Manually):

Step1 : In the first step you would need to print the data you are going to fill the drop down list with somewhere. Usually I open a new sheet, name it something no one would ever consider using (like “far43fq”) and print the data there.

Excel VBA, Drop Down Lists, Data Validation Data

Step 2: Select the cell you would like to add the drop down list to. Then click on the Data Validation button on the Data Ribbon:

Adding a Data Validation to a specific cell, Excel VBA

Step 3: Select list:

Selecting the list type data validation from the list of available data validation types excel vba

Step 4: Input the range of the data. If the drop down list (data validation) and the data are in the same sheet you would reference them using a statement like “=A1:A6”. If they are in separate sheets you would use a statement like “=SheetName!A1:A6”, where “SheetName” is replaced with the name of the sheet.

Excel VBA, Drop Down Lists, Data Validation Input Data

Note: I this example the input data is in another sheet. The name of the sheet is Sheet1″.

After pressing Ok your drop down list is ready:

A drop down list created using data validation


Creating Data Validation (Using VBA):

Using the code below a drop down list (data validation) will be created in the cell “J2” . The data for the drop down list will come from the range “=A1:A6” in the sheet “Sheet1”. Note you must change the highlighted parts based on the location of your source and the location for your drop down list:

Private Sub main()

'replace "J2" with the cell you want to insert the drop down list
With Range("J2").Validation
.Delete
'replace "=A1:A6" with the range the data is in.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:= xlBetween, Formula1:="=Sheet1!A1:A6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


Selection Change:

The data validation itself doesn’t have a built in function for determining when the user has selected a new value. Though you could use the worksheet_change event handler to determine when the user has selected a new value from the drop down list. The worksheet_change event triggers every time changes are made to a worksheet. You could use the worksheet_change event handler to catch this event and check if the changes made were to the value selected in the drop down list.

The code below is a worksheet_change event handler. It checks if the changes in the worksheet have occurred in the cell with the drop down list or not:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("J2").Address Then
'your code
End If
End Sub


Modifying, Adding, Inserting and Removing Items (Usin VBA):

In order to modify, add, insert and remove items from a drop down list created using data validation, you would have to follow 2 steps.

Step 1: The first thing you would have to do is change the source data. For example lets say we want to modify the second item to “New Item 2”, we would need to change the data validation’s source to the values below:

Excel VBA, Drop Down Lists, Data Validation Data, Modified Data
Or for example lets say we want to add an item to the list of items. Again the first thing would be to modify the source data:

Excel VBA, Drop Down Lists, Data Validation Data, Modified Data 2
Or for example lets say we want to remove “item 4”. Again the first step would be to modify the source data:

Excel VBA, Drop Down Lists, Data Validation Data, Modified Data 3
Step 2: In the next step we need to update the drop down list to accommodate for the changes made in its source. This can be done using the code below. The code below must be copied to the sheet with the source data. The highlighted parts must be changed based on the location of your source data and the location you would like the drop down list to appear:

Option Explicit
Dim flagProgram As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intRowCount As Integer
If flagProgram = False Then
flagProgram = True

'get the total rows of data
intRowCount = Get_Count
'update the drop down list(data validation)
Call Update_DataValidation(intRowCount)

flagProgram = False
End If

End Sub

'This function will return the total count of rows in the
'drop down list(data validation) source
Private Function Get_Count() As Integer
'counter
Dim i As Integer
'determines if the we have reached the end
Dim flag As Boolean

i = 1
flag = True
While flag = True
If Cells(i, 1) <> "" Then
'if there is still data go on
i = i + 1
Else
'if there is no more data left stop the loop
flag = False
End If
Wend

'return the total row count
Get_Count = i - 1
End Function

'the function below updates the source range for the data validation
'based on the number of rows provided by the input
Private Sub Update_DataValidation(ByVal intRow As Integer)
'the reference string to the source range
Dim strSourceRange As String

strSourceRange = "=Sheet1!A1:A" + Strings.Trim(Str(intRow))
With Sheet2.Range("J2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:= xlBetween, Formula1:=strSourceRange
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

The code above has 3 different function. The main function is Worksheet_Change event handler. The event handler executes when the user makes changes to the sheet with the source data:

Private Sub Worksheet_Change(ByVal Target As Range)
...
End Sub

flagProgram determines if the current changes made to the sheet have been done by the program or the user. This is to prevent an endless recursion of the Worksheet_Change event handler:

If flagProgram = False Then
flagProgram = True
...
flagProgram = False
End If

The line below gets the number of rows in the source for the data validation. This value must be checked each time to account for added and removed items:

intRowCount = Get_Count

The function Update_DataValidation updates the data validation based on the input parameter intRow. The input parameter intRow determines how many rows of data the drop down list must use. The first line of this function creates a string which is a reference to the range with the source data:

strSourceRange = "=Sheet1!A1:A" + Strings.Trim(Str(intRow))

Note the highlighted section must changed if your source data is not in Sheet1 starting from cell A1. The resulting string will be something like this:

“=Sheet1!A1:A5”

or

“=Sheet1!A1:A7”

For more information about string processing and manipulation please see the link below:

The rest of the lines in the function Update_DataValidation creates a drop down list in the cell “J2” in sheet2.

You can download the file and code related to this article from the link below:

See also:

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com

15 thoughts on “Excel VBA Drop Down Lists Using Data Validation”

  1. Satish says:

    if I have name multiple time than how do I create data validation

    1. pedrumj says:

      Hi there

      I don’t think duplicates effect data validation. Please send me your workbook so I could have a look at it.

  2. FRANK ZHAO says:

    I downloaded the file you provide,but when I run the code,why always a macro window jump out? an if I change the range from “J2” to any other place nothing happened.

    1. FRANK ZHAO says:

      Other thing is that how can I modify this scrip if all my drop-down list value is from a row?

      1. pedrumj says:

        You could use something like this:

        Option Explicit
        Dim flagProgram As Boolean
        
        Private Sub Worksheet_Change(ByVal Target As Range)
        Dim intRowCount As Integer
        If flagProgram = False Then
            flagProgram = True
        
            'get the total rows of data
            intRowCount = Get_Count
            'update the drop down list(data validation)
            Call Update_DataValidation(intRowCount)
        
        
            flagProgram = False
        End If
        
        End Sub
        
        'This function will return the total count of rows in the
        'drop down list(data validation) source
        Private Function Get_Count() As Integer
        'counter
        Dim i As Integer
        'determines if the we have reached the end
        Dim flag As Boolean
        
        i = 1
        flag = True
        While flag = True
            If Cells(1, i) <> "" Then
                'if there is still data go on
                i = i + 1
            Else
                'if there is no more data left stop the loop
                flag = False
            End If
        Wend
        
        'return the total row count
        Get_Count = i - 1
        End Function
        
        'the funtion below update the source range for the data validation
        'based on the number of rows provided by the input
        Private Sub Update_DataValidation(ByVal intRow As Integer)
        'the reference string to the source range
        Dim strSourceRange As String
        
        strSourceRange = "=Sheet1!" & Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, intRow)).Address
        With Sheet2.Range("J2").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=strSourceRange
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        End Sub
        
        
    2. pedrumj says:

      Hi there

      Its working fine on my side. Could you please tell me what you’re doing that’s causing it to crash?

      1. FRANK ZHAO says:

        Hi,pedrumj,

        I tried to run your script,but in first sub,I cant use F8 to debug that in step,and when I run the “Update_DataValidation”,there is always a macro window jump out,so cant run it,I dont know why,can you help me ? Thanks.

        1. pedrumj says:

          Hi

          Please send me the workbook you have created so that I could have a look at it.

      2. FRANK ZHAO says:

        strSourceRange = “=Sheet1!” & Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, intRow)).Address

        there is always come out a error at here

  3. amymon87 says:

    Hi,

    I am trying to select the first item on a manually created list using VBA code in excel and cant seem to find anywhere how to do it?

    Thanks

  4. amymon87 says:

    Hi,

    I am trying to select the first item on a manually created drop down list using VBA code in excel and cant seem to work out how to do it?

    Cheers

    1. pedrumj says:

      Hi there

      Well data validation is not really a drop down list. It just looks like one. If you want to programmatically select the first item, you will need to select the first cell in the range were the data validation gets is values from.

      Please let me know if you are still experiencing any trouble on this.

  5. ashish says:

    If you are interested in learning Select From Drop Down And Pull Data From Different Sheet

    http://www.exceltip.com/tips/select-from-drop-down-and-pull-data-from-different-sheetin-microsoft-excel-2010.html

  6. Travis says:

    I am unable to download the .xlsm file (probably due to a fire wall). Is there any way you can send me a text file of the complete code from this page so I can import it into my excel file for testing?

  7. Uday Sandhu says:

    Hey,

    is it possible to get excel to automatically select from the drop down list and every time it makes a selection copy the data into a new sheet? Im working with 100’s of companies so instead of selecting each one at a time and doing the standard analysis i would like to run them for the whole database. Unfortunately i only have the consolidated file so i do not know the source of the list. Could you please help with the code, or even an example?
    THanks.

Leave a Reply

Your email address will not be published. Required fields are marked *