Drop Down List Creator (Sample Code and Download)

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:

'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:

Excel, VBA Input Data for Data validation (Drop Down List)

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:

Excel, VBA Drop Down List

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

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

Leave a Reply

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

privacy policy