Double click cell browse for file and paste path

[drop05]

Hi, i wanted to see if there is a way to double click a cell in a range, allow the user to browse for a file and select it, not open it, but get the file path and paste the file path in the cell next to the cell double clicking

 

[Bosquedeguate]

How are your VBA coding skills?

Create a double_click event for the Sheet where you want this to be implemented.

The following code does nothing, but it does display the file dialog box. This should give you a start on implementing what you need.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target(1, 1).Address = "$A$4" Then
    FileDialog
  End If
End Sub

Private Function FileDialog() As String
 
'Requires reference to Microsoft Office 12.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
 
   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Do not allow user to make multiple selections in dialog box.
      .AllowMultiSelect = False
             
      'Set the title of the dialog box.
      .Title = "Please select one or more files"
 
      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access Databases", "*.XL*"  'edit for desired file types
      .Filters.Add "All Files", "*.*"
 
      If .Show = True Then
        'add code here for what to do with selected file name
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Function

 

[Logit]

Paste the following in the Sheet level module :

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim DialogBox As FileDialog
Dim FileType

Dim path As String

Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)

DialogBox.Title = "Select file for " & FileType

DialogBox.Filters.Clear

DialogBox.Show

If DialogBox.SelectedItems.Count = 1 Then

    path = DialogBox.SelectedItems(1)
    ActiveCell.Offset(0, 1).Value = path
    Range("A1").Select

End If

End Sub