Insert filename via a browse to cell

[Rookie_User]

I would like to click in column A. Let's say cell A3 and browse to a file on
the network and then click on the file and have the filename automatically
inserted in the cell. Maybe even the path - but it just has to be the name.
Then if I move to cell A4, would like the same functionality. Or if you have
a different method. We have a process that creates files, some have errors
and I want to document the file and it's error -- however the filename is
huge.

 

[Tom Hutchins]

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

 

[Rookie_User]

That worked PERFECTLY!!! thank you so much for your time and efforts.