Paste the following code in the code module for the sheet where you want his
to work (double-clicking any cell in column A will display a File Open
dialog. The name of the file you select will be entered into the cell).
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim Fyle As String
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
Fyle$ = Application.GetOpenFilename("All Files (*.*), *.*")
'If no file was selected (Cancel clicked), stop the macro.
If Fyle$ = "False" Then Exit Sub
'To include the path, just assign Fyle$ to Target.Value
Target.Value = InStrR(Fyle$, "\")
End Sub
Private Function InStrR(CheckThis As String, ForThis As String) As String
'Searches a string for a specified character from the END to the BEGINNING.
'If found, returns the rest of the string, starting one character to the
right
'of ForThis.
'Declare local variables.
Dim xx As Integer, TmpStr1 As String
'Make sure we are only searching for a single character.
If Len(ForThis$) <> 1 Then
MsgBox "InStrR only searches for a single character", _
vbExclamation, "ERROR"
InStrR$ = vbNullString
End If
'Walk backwards through CheckThis$ one character at a time.
For xx% = Len(CheckThis$) To 1 Step -1
Select Case Mid(CheckThis$, xx%, 1)
'If encounter ForThis character, stop & return TmpStr1$.
Case ForThis$
InStrR$ = Trim(TmpStr1$)
Exit Function
'If encounter any other character, add it to the FRONT of TmpStr1$ (reverses
'the order).
Case Else
TmpStr1$ = Mid(CheckThis$, xx%, 1) & TmpStr1$
End Select
Next xx%
End Function
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Hope this helps,
Hutch
