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

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,589
Office Version
  1. 2021
Platform
  1. Windows
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

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


Kindly test & amend my code


See full code below



Code:
 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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try:
VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top