CODEWITHSUNDEEP
excelvba
AnalysisLearner
AnalysisLearner

Reputation: 115

Excel VBA create dropdown list

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.

Upvotes: 1

Views: 3932

Answers (1)

Vityata
Vityata

Reputation: 43585

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

enter image description here

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

Upvotes: 1

Related Questions