Excel VBA create dropdown list

Question

I'm trying to write some code that will create a dropdown list that contains all files with a certain file extension in a folder. Initial code here:

Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object
Dim fp As String
Dim i As Integer    

fp = Environ("UserProfile") & "\OneDrive\Desktop\Test"

Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(fp)
Set FSOFile = FSOFolder.Files

i = 1

For Each FSOFile In FSOFile
    If FSOFile Like "*.txt*" Then
        'just put the name into column B for testing
        Range("B" & i).Value = FSOFile.Name
        i = i + 1
    End If    
Next FSOFile

Obviously I need to add the data validation part of the code in, but I'm not sure how best to construct it. The number of files is dynamic.

I was thinking of putting all the files that match the desired file extension type into an array, and then writing each entry of the array into the data validation section?

I've seen the Dir() used quite a lot but I don't fully understand it, so have opted to use fso.

Where are you trying to create the drop-down? In a worksheet cell? In a Worksheet FormControl? In a UserForm? — Chronocidal
– Chronocidal, Commented Nov 5, 2019 at 15:31
Just in a worksheet cell to make it simple for the end-user. — AnalysisLearner
– AnalysisLearner, Commented Nov 5, 2019 at 15:35

Vityata · Accepted Answer · 2019-11-05 16:04:32Z

1

Using Excel Range

Building dropdown usually consists of 2 things:

locating the range, which is used for the list of the values
writing these values to a string, separated with a comma

The code below, does exactly that:

First it loops from row 1 to row endRow, to the values in these rows. These are written to a string validationString and a comma is added after every cell value
the last comma is useless, thus it is removed, together with the space: validationString = Left(validationString, Len(validationString) - 2)
the validationString is passed to the .Validation property of the cell "A1".

Sub TestMe()

    Dim wks As Worksheet: Set wks = Worksheets(1)
    Dim endRow As Long: endRow = LastRow(wks.Name, 3)
    Dim validationString As String
    Dim i As Long

    For i = 1 To endRow
        validationString = validationString & wks.Cells(i, "C") & ", "
    Next i

    validationString = Left(validationString, Len(validationString) - 2)

    With Worksheets(1).Cells(1, "A").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:=validationString
    End With

End Sub


Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long
    Dim ws As Worksheet
    Set ws = Worksheets(wsName)
    LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row
End Function

Without writing to a range

Here the "trick" is take the data from the loop and write it to a list, while looping through the fsoFolder.Files:

Sub TestMe()

    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 "*.txt*" Then
            validationString = validationString & fsoFile.Name & ", "
        End If
    Next fsoFile

    validationString = Left(validationString, Len(validationString) - 2)

    With Worksheets(1).Cells(1, "A").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:=validationString
    End With

End Sub

edited Nov 5, 2019 at 16:04

answered Nov 5, 2019 at 15:38

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

AnalysisLearner Over a year ago

Thanks for the response. So would I have to write each of the file names to a range of cells, then create the data validation (similar to how you've done). Or is there some way to avoid writing the file names? Ideally the user will be able to just click the dropdown see a list of relevant files, select one, and then run a macro that takes that filename as a parameter.

SmileyFtW Over a year ago

Instead of worrying about how many items there are and collecting them all into a validation string I'd suggest simply using a dynamic named range. Then use the dynamic named range's name as the validation string. IOW all that would change is the list of files in the dynamic range. The validation would not need to be touched.

Vityata Over a year ago

@AnalysisLearner - if you do not write the names, it becomes a little more "magical". In general, there is a way - concatenate the validationString from the loop in your code like this - validationString = validationString & FSOFile.Name & ", ". Then remove the last 2 signs and use the code to write the validation.

Vityata Over a year ago

@SmileyFtW - named range is a good alternative indeed. Anyway, the string is a bit better, if the OP does not want to write anything in the Excel file (see his first comment)

AnalysisLearner Over a year ago

Thanks, both. Some cool new ideas for me in here. (It won't let me upvote as not enough rep).

|

Collectives™ on Stack Overflow

Excel VBA create dropdown list

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related