Dropdown list of folder names/dropdown list of subfolders of selected folder/excel files

[adokskel]

Hello Everybody,

I wish you ease at your work.
I am not an excel pro. Just wanted to know if this is easy for you to help.
If not just forget it. I dont want to waste your time.

- I wish a dropdown list at first cell of first coloumn which will be listed the name of folders which are located in a folder named "Main" at desktop. Then I will select a folder.
- Another dropdown list at first cell of second coloumn which will be listed the subfolder names which are loceted in the folder that I selectat previous step.
- Another dropdown list at first cell of third coloumn which will be listed the names of excel files which are loceted in the subfolder that I select at previous step. Then I select an excel files and want it to open.

Waiting for your valuable responds.

Best regards.

 

[RoryA]

Please take a minute to read the forum rules, especially regarding cross-posting, and comply with them. (they are much the same on all Excel forums). Thank you.

 

[adokskel]

Please take a minute to read the forum rules, especially regarding cross-posting, and comply with them. (they are much the same on all Excel forums). Thank you.

Hello Dear Admin,
Sorry for my fault.
I will add the links to my first post immediately.

 

[adokskel]

Hello Everybody,

I wish you ease at your work.
I am not an excel pro. Just wanted to know if this is easy for you to help.
If not just forget it. I dont want to waste your time.

- I wish a dropdown list at first cell of first coloumn which will be listed the name of folders which are located in a folder named "Main" at desktop. Then I will select a folder.
- Another dropdown list at first cell of second coloumn which will be listed the subfolder names which are loceted in the folder that I selectat previous step.
- Another dropdown list at first cell of third coloumn which will be listed the names of excel files which are loceted in the subfolder that I select at previous step. Then I select an excel files and want it to open.

Waiting for your valuable responds.

Best regards.

This thread is also asked the forums below:

Dropdown list of folder names/dropdown list of subfolders of selected folder/excel files - OzGrid Free Excel/VBA Help Forum
Dropdown list of folder names/dropdown list of subfolders of selected folder/excel files
excelguru.ca/forums/showthread.php?10354-Dropdown-list-of-folder-names-dropdown-list-of-subfolders-of-selected-folder-excel-f&p=42665#post42665

 

[Yongle]

Test this in a NEW workbook and then we know that it cannot conflict with anything else

Insert the code below in sheet1's SHEET module (will not work if placed anywhere else)

To test
click on D1 (or any cell other than A1:C1)
click on A1 & select item from dropdown
click on B1 & select item from dropdown
click on C1 & select item from dropdown

The code
right click on sheet tab \ View Code \ paste code into that window \ shortcut {ALT}{F11} takes you back to Excel

Option Explicit
Const celA = "A1", celB = "B1", celC = "C1", PS = "\"
Private A As Range, B As Range, C As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim List As String, Main As String
    Main = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Main"
    Set A = Range(celA): Set B = Range(celB): Set C = Range(celC)
    If Selection.CountLarge > 1 Then Exit Sub
   
    If Not Intersect(Target, Union(A, B, C)) Is Nothing Then
        Select Case Target.Address(0, 0)
            Case celC:  List = FileList(Main & PS & A & PS & B)
            Case celB:  List = FolderList(Main & PS & A): Union(B, C).ClearContents
            Case celA:  List = FolderList(Main): Union(A, B, C).ClearContents
        End Select
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Join(Array(List), ",")
        End With
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Selection.CountLarge > 1 Then Exit Sub
    Application.EnableEvents = False
    On Error Resume Next
    Select Case Target.Address(0, 0)
        Case celA: Union(B, C).ClearContents
        Case celB: C.ClearContents
        Case celC: Workbooks.Open (A & PS & B & PS & C)
    End Select
    Application.EnableEvents = True
End Sub

Private Function FileList(filePath As String)
    Dim Fyle As Object, F As String
    F = ","
    For Each Fyle In CreateObject("Scripting.FileSystemObject").GetFolder(filePath).Files
         F = F & "," & Fyle.Name
    Next Fyle
    FileList = Replace(F, ",,", "")
End Function

Private Function FolderList(folderPath As String)
    Dim subFolder As Object, F As String
    F = ","
    For Each subFolder In CreateObject("Scripting.FileSystemObject").GetFolder(folderPath & "\").subfolders
        F = F & "," & subFolder.Name
    Next
    FolderList = Replace(F, ",,", "")
End Function

Note
Error handling ignored for this first effort - it must be added later to make the routines robust

Problems whilst testing
If above does not work for you ... reply with
- the line where the code failed
- and the full details of any error message

After Testing
- Does the above do what you want in the way you want ?
- What has testing revealed that should be changed ?

 

[adokskel]

Hello Dear Yongle,

Thanks for your time and your code.

- No error message appears but is it possible to execute the excel file at C1 by clicking on it. ?

- I have just realized that dropdown list is different from ordinary "filter".
Is it easy to change it with filter because I want to enter some letters from keyboard to limit listed costumers, part names to find and select quickly. Just I am asking.

Again thanks for your time and codes.
Best Regards.

 

[Yongle]

You could add a Before_RightClick event to the code in post#5 above

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Selection.CountLarge > 1 Then Exit Sub
    Application.EnableEvents = False
    On Error Resume Next
    If Target.Address(0, 0) = celC Then
        Cancel = True
        Workbooks.Open (A & PS & B & PS & C)
    End If
    Application.EnableEvents = True
End Sub

Before_DoubleClick would also work with same code, replacing the name to Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

 

[adokskel]

Hello Again Dear Yongle,
Red rectangle is existing codes from Post#5 Green recrangle is added code from post#7.
- Still can not open listed excel file at the cell C1
- Possible to change dropdown list with "filter" option.
Best Regards

 

[Yongle]

No error message appears but is it possible to execute the excel file at C1 by clicking on it. ?

Did the workbook open when you selected a value in C1 ?

 

[adokskel]

No