Using GetOpenFilename from a cell

[Raj]

Hi,

I want the GetOpenFilename file selection dialog box to open when the
user double clicks in a cell. The file cell selected should then
become the cell value. If cancel is selected, the existing cell value
should remain unchanged.

How do I do using VBA code?

Thanks in advance for the help.

Regards,
Raj

 

[Tim Williams]

In the worksheet code module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim s
If Target.Row = 1 And Target.Column = 1 Then
s = Application.GetOpenFilename()
If s <> False Then
Target.Value = s
Else
Target.Value = "" 'clear on cancel ?
End If

Cancel = True
Else
Cancel = False
End If

End Sub


Tim

 

[Corey ....]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If ActiveCell.Value = ActiveWorkbook.Name Then
MsgBox "Activeworkbook name = " & ActiveCell.Value
End If
End Sub

 

[Raj]

Hi Tim,

I tried the code. As it did not work, I modified it as shown under:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim s
If Not Application.Intersect(Target, Me.Range("b2")) Is Nothing Then
'If Target.Row = 1 And Target.Column = 2 Then
s = Application.GetOpenFilename()
If s <> False Then
Target.Value = s
Else
Target.Value = "" 'clear on cancel ?
End If


Cancel = True
Else
Cancel = False
End If


End Sub

As the modification was cosmetic, I did not expect it to work. It
didn't.
Now the actual behaviour: When I double-click in cell B2, it opens for
editing (which is the default behaviour of double-clicking in a cell).
Has some code to be written to disable this default behaviour.
Incidentally, I have double-checked that the code is in the right work
worksheet, macros are enabled and application.enableevents = True

Please Help.

Thanks and Regards,
Raj

 

[Tim Williams]

Raj,

I can only say "it worked for me" (XP, XL2002). Setting Cancel to true
should cancel the default double-click action (ie. going into edit mode).

Do you by any chance have an (eg) XL add-in which might also be capturing
the doubleclick event from the sheet ?

If you wanted the cell to remain unchanged if the user cancels, then remove
the lines.

Else
Target.Value = ""

If you're still having problems then I can mail you my test file. My email
is
t i m j williams at g mail dot co m (no spaces)

Tim


Hi Tim,

I tried the code. As it did not work, I modified it as shown under:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim s
If Not Application.Intersect(Target, Me.Range("b2")) Is Nothing Then
'If Target.Row = 1 And Target.Column = 2 Then
s = Application.GetOpenFilename()
If s <> False Then
Target.Value = s
Else
Target.Value = "" 'clear on cancel ?
End If


Cancel = True
Else
Cancel = False
End If


End Sub

As the modification was cosmetic, I did not expect it to work. It
didn't.
Now the actual behaviour: When I double-click in cell B2, it opens for
editing (which is the default behaviour of double-clicking in a cell).
Has some code to be written to disable this default behaviour.
Incidentally, I have double-checked that the code is in the right work
worksheet, macros are enabled and application.enableevents = True

Please Help.

Thanks and Regards,
Raj

 

[Raj]

Hi Tim,

Thanks a lot. It's working now. Maybe, I had goofed up somewhere
earlier.

Regards,
Raj