For Each Folder In Directory

[jbrouse]

Greetings,

Can someone please assist me in writing a For statement that will add the name of each folder in a certain directory to my dropdown menu in the code below? Is this even possible?

Thanks in advance!

Private Sub Workbook_Open()
    
    Const myPath = "H:\Common3\Process Flows\"
    Dim sht As Worksheet
    On Error Resume Next
    Application.CommandBars("Process Flow").Delete
    On Error GoTo 0
    With Application.CommandBars.Add("Process Flow", , False, True)
        With .Controls.Add(msoControlDropdown)
            For Each ??? In ???
                    .AddItem "" & Folder.Name
            Next ???
            .TooltipText = "SheetNavigate"
        .OnAction = "Sheet_Navigate"
        .Width = 120
        End With
        .Protection = msoBarNoCustomize
        .Position = msoBarFloating
    .Visible = True
    End With
End Sub

 

[tusharm]

Check out XL VBA help for the DIR() function.