open files from listbox on userform within subfolder

Mussala

As I said earlier, a folder name should not end in ".pdf".

Revised Answer 14 July 2023

From your clarification comments, you want to search for pdfs in a top-level folder (e.g. your C:\Users\MU\Desktop\pdf Folder\) and any subfolders, looking for a file matching the item clicked in the ListBox (e.g. NO_0003135 from the SALES worksheet).

In the attached revised file, I've changed the code so it searches the top-level then calls a function to search any subfolders. That function calls itself iteratively so the first line:

If Fnd <> vbNullString Then Exit Function

stops it going further after a match is found. In other words it stops when it finds the first match (so file names should be unique).

It relies on the variables declared at the top of the UserForm code page, so:

Public Sk As String, Fnd As String

where Sk is intended for what we seek (e.g. an Invoice Number) in the folder (and subfolders) and Fnd is the full path of what we find (if anything).

If the main code or function finds a filename (e.g. NO_000313.pdf) matching the picked item, it will offer you the choice of opening the file. If not it tells you it couldn't be found. 

Your click procedure below is modified (and other users must change the top-level path in bold): My comments should help you to follow it:

Private Sub ListBox1_Click()
    Dim Rply As Long

    Set objFso = CreateObject("Scripting.FileSystemObject")

    On Error Resume Next

    With ListBox1
        'change path to suit
        Set objFolder = objFso.GetFolder("C:\Users\MU\Desktop\pdf Folder\")

        'clear found public varable
        Fnd = vbNullString

        If objFolder Is Nothing Then
            MsgBox "Please check macro- the named folder does not exist"
            Exit Sub
        End If
        ' set public file name to find
        Sk = LCase(.List(.ListIndex, 1)) & ".pdf"
        ' loop through top level folder
        For Each objSubFile In objFolder.Files
            If LCase(objSubFile.Name) = Sk Then
                ' if found, write file path to found variable
                Fnd = objSubFile.Path
                'stop looking
                Exit For
            End If
        Next objSubFile

        ' if not yet found, call the function to loop through its subfolders (and theirs)
        If Fnd = vbNullString Then LoopEachFolder objFolder, Sk

        ' quit FSO
        Set objFso = Nothing


        ' If a file was found...
        If Fnd <> vbNullString Then
            Rply = MsgBox(Fnd & " was found." & vbCr & vbCr & "Open that file?", vbYesNo, "File found...")
            ' ... open pdf if yes
            If Rply = vbYes Then apiShellExecute 0, "Open", Fnd, "", "", vbMaximizedFocus

            Else
            ' otherwise tell user
                MsgBox "Sorry but the file " & vbCr & .List(.ListIndex, 1) & ".pdf" _
                        & " was not found in " & objFolder & vbCr _
                        & " (or its subfolders)"
        End If

    End With

End Sub

Function LoopEachFolder(fldFolder As Object, Sk As String)

    ' With the subfolders in this folder....
    For Each objFldLoop In fldFolder.subFolders
        ' if already found, quit any iterative functions
        If Fnd <> vbNullString Then Exit Function
        ' ...otherwise loop through files in this folder
        For Each objSubFile In objFldLoop.Files
            If LCase(objSubFile.Name) = Sk Then
                ' found so add file path to found variable
                Fnd = objSubFile.Path
            End If
        Next objSubFile
        ' ... if not found, run the function on each subfolder found
        If Fnd = vbNullString Then LoopEachFolder objFldLoop, Sk

    Next objFldLoop

End Function

Hope this fixes things for you.