VBA Code to double click on Folder in Col A and folder to opened and user to be able to select file

[howard]

I have list of file directories in Col A. I want to be able to double click on the cell and the file directory to be opened to sellect the file to be opened via Excel

I have the following code below , but get bad file or number

 If Len(FolderPath) > 0 And Dir(FolderPath, vbDirectory) <> "" Then

Kindly test & amend my code


See full code below

 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim SelectedFolderPath As String
    Dim FolderPath As String
    
    ' Get the path of the folder that was selected in Windows Explorer
    SelectedFolderPath = GetSelectedFolderPath()
    
    ' Check if the double-clicked cell is in column A and if the path is valid
    If Target.Column = 1 And Len(SelectedFolderPath) > 0 And Dir(SelectedFolderPath, vbDirectory) <> "" Then
        ' Get the full path of the selected folder
        FolderPath = SelectedFolderPath & "\" & Target.Value
        
        Debug.Print "FolderPath = " & FolderPath ' Add this line for debugging purposes
        
        ' Check if the folder exists
        If Len(FolderPath) > 0 And Dir(FolderPath, vbDirectory) <> "" Then
            ' Open the folder in Windows Explorer
            Shell "explorer.exe """ & FolderPath & """", vbNormalFocus
        End If
    End If
End Sub

   

Function GetSelectedFolderPath() As String
    Dim objShell As Object
    Dim objFolder As Object
    Dim objFolderItem As Object
    
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace("C:\")
    Set objFolderItem = objFolder.Self
    
    GetSelectedFolderPath = objFolderItem.Path
End Function

 

[mumps]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column <> 1 Then Exit Sub
    Dim dialog As FileDialog, filePath As String, directory As String
    directory = Target
    Set dialog = Application.FileDialog(msoFileDialogFilePicker)
    With dialog
        .AllowMultiSelect = False
        .InitialFileName = directory
        .Show
        If .SelectedItems.Count <> 0 Then
            filePath = .SelectedItems.Item(1)
            Workbooks.Open (filePath)
        End If
    End With
End Sub

 

[howard]

Thanks for the help. Code works 100%

 

[mumps]

You are very welcome.