The following code is a function I’ve written which creates drop down lists. The drop down lists are created using data validation. It is assumed that there is a column with the drop down list values on one of the sheets. Another feature of this function, is that after it creates the drop down list it checks if the value in drop down list cell is actually part of the input or not. If the value isn’t part of the input values it will update it with the first cell from the input values. This is useful if the list values change based on user action. You can download the code and files related to this article here:

Jump To:

• Input Parameters
• Example

'Creates a data validation list in the output sheet,
'from the data in the input sheet
'intCount_Input: The total amount of items to add to the data validation
'intRow_Input: The starting row to get the data validation values
'intColumn_Input: The column to get the data validation values
'strWorksheet_Input: The name of the input workhseet
'intRow_Output: The row to put the data validation
'intColumn_Output: The column to put the data validation
'wrksheet_output: The worksheet to put the data validation

Public Sub Create_DataValidation(ByVal intCount_Input, _
ByVal intRow_Input As Integer, ByVal intColumn_Input As _
Integer, ByVal strWorksheet_Input As String, ByVal _
intRow_Output As Integer, ByVal intColumn_Output As Integer, _
ByRef wrksheet_Output As Worksheet)

If intCount_Input <> 0 Then

    With wrksheet_Output.Range(wrksheet_Output.Cells( _
    intRow_Output, intColumn_Output), _
        wrksheet_Output.Cells(intRow_Output, _
        intColumn_Output)).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="='" + strWorksheet_Input _
        + "'!" + Get_Alphabet(intColumn_Input) + _
        Strings.Trim(Str(intRow_Input)) + ":" + _
        Get_Alphabet(intColumn_Input) + Strings.Trim(Str(intRow_Input _
        + intCount_Input - 1))
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
   
    If findStr(wrksheet_Output.Cells(intRow_Output, intColumn_Output), _
    intRow_Input, intColumn_Input, Sheets(strWorksheet_Input), _
        intCount_Input) = False Then
    wrksheet_Output.Cells(intRow_Output, intColumn_Output) = _
    Sheets(strWorksheet_Input).Cells(intRow_Input, intColumn_Input)
    End If
Else
    With wrksheet_Output.Range(wrksheet_Output.Cells(intRow_Output, intColumn_Output), _
        wrksheet_Output.Cells(intRow_Output, intColumn_Output)).Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ErrorTitle = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    wrksheet_Output.Cells(intRow_Output, intColumn_Output) = ""
End If
End Sub

'Determines if the selected string exists in the selected column
'strTarget: The value to search for
'intRow: The column to start searching at
'intColumn: The column to search
'wrksheet: The worksheet to search
'intCount: The number of data to search

Public Function findStr(ByVal strTarget As Variant, ByVal intRow _
As Integer, ByVal intColumn As Integer, _
ByRef wrkSheet As Worksheet, ByVal intCount As Integer) As Boolean
   
Dim arrValues As Variant
Dim i As Integer
ReDim arrValues(intCount - 1)

If intCount <> 0 Then

    arrValues = wrkSheet.Range(wrkSheet.Cells(intRow, intColumn), _
    wrkSheet.Cells(intRow + intCount - 1, intColumn)).Value

' case the input is an array
On Error GoTo lblNotArray:
    For i = 1 To intCount
        If arrValues(i, 1) = strTarget Then
        findStr = True
                Exit Function
        End If
       
    Next i
End If

findStr = False

Exit Function
lblNotArray:
If arrValues = strTarget Then
    findStr = True
Else
    findStr = False
End If

End Function

'''
'Returns the alphabet associated with the column
'intNumber: The column number
'Return Value: Alphabet associated with the column number
Private Function Get_Alphabet(ByVal intNumber As Integer) As String
Get_Alphabet = Strings.Trim(Chr(intNumber + 64))
End Function


Input Parameters:

intCount_Input: The number of items that need to be on the list.

intRow_Input: The row where the drop down lists values start. As mentioned above it is assumed that there is column in one of the sheets with the drop down list values.

intColumn_Input: The column where the drop down list values are. As mentioned above it is assumed that there is column in one of the sheets with the drop down list values.

strWorksheet_Input: The name of the worksheet where the drop down list values are. For more information about worksheets and their names please see Excel VBA Working With Sheets.

intRow_Output: The row where you want the drop down list to appear.

intColumn_Ouput: The column where you want the drop down list to appear.

wrkSheet_Output: The worksheet where you want the drop down list to appear. Note that this is a reference to the worksheet object. For more information about the worksheet object please see Excel VBA Working With Sheets.

Example:

In this example the input values for the drop down list are in sheet2 starting from cell A2:

Assuming we want the drop down list to appear in cell B2 on sheet1, we would use the code below:

Sub Example()
Call Create_DataValidation(8, 2, 1, "Sheet2", 1, 2, Sheet1)
End Sub 

Result:

You can download the code and files related to this article here.

See Also:

• Excel VBA Working With Sheets
• Excel VBA Drop Down Lists
• Excel VBA Working With Worksheets

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