Dropdown lists in Excel usually are built from a range or from a variable list.
Dropdown list from Excel Range
Thus, building a dynamic dropdown list may include building a dynamic range, with undeclared end. Thus, let’s imagine that the end of the range is unknown and it should be used for the dropdown list.
Thus, once we know the start, the end could be found with the LastRow function:
|
1 2 3 4 5 |
Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets(wsName) LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row End Function |
The rest is easy – we should know which range to consider for the validation. Passing the range looks like this =C1:C5 and converted to a VBA formula, this looks like the following:
|
1 2 |
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=" & ValidationRange.Address |
The whole code looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub ValidationRangeAddress() Dim wks As Worksheet: Set wks = Worksheets(1) Dim endRow As Long: endRow = LastRow(wks.Name, 3) Dim ValidationRange As Range Set ValidationRange = wks.Range(wks.Cells(1, 3), wks.Cells(endRow, 3)) With Worksheets(1).Cells(1, "A").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:="=" & ValidationRange.Address End With End Sub |
Dropdown list from Named Range
Using named range for a source of a dropdown gives one huge advantage over using a normal range – if a new value is added to the range, the validation is automatically edited. Thus, we get quite dynamic range.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub ValidationNameRange() Dim wks As Worksheet: Set wks = Worksheets(1) Dim nameString As String Dim nameRange As Range nameString = "validator" Set nameRange = wks.Range("C1:C5") ThisWorkbook.Names.Add Name:=nameString, RefersTo:=nameRange With Worksheets(1).Cells(1, "A").validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:="=" & nameRange.Address End With End Sub |
Dropdown list from variable list
Using a range for a source for the dropdown in general has 1 drawback – we should write the range somewhere in Excel. And sometimes, when the Excel file becomes too large, it is not always handy to look for the “Settings” tab and try to edit it back. Thus, it is possible to write the range from a list, which is simply inserted through VBA. In the example below, there is a folder “QA” in the desktop, accessed through Environ("UserProfile") & "\Desktop\QA". In this folder, there are a few *.xlsx files, which are converted through a dropdown. Without writing them to the Excel file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Sub DropdownList() Dim filePath As String filePath = Environ("UserProfile") & "\Desktop\QA" Dim fsoLibrary As Object: Set fsoLibrary = CreateObject("Scripting.FileSystemObject") Dim fsoFolder As Object: Set fsoFolder = fsoLibrary.GetFolder(filePath) Dim fsoFile As Object Dim validationString As String For Each fsoFile In fsoFolder.Files If fsoFile Like "*.xlsx" Then validationString = validationString & fsoFile.Name & ", " End If Next fsoFile If validationString <> "" Then validationString = Left(validationString, Len(validationString) - 2) With Worksheets(1).Range("A1").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:=validationString End With End If End Sub |
Thank you for reading! 🙂