Since application.GetOpenFilename shows the file open dialog, I believe
showing it twice would be noticeable.

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
Hi Lorne
1. of cource both solutions would work but assigning the result of the
function call to a variable prevents multiple evaluation the function.
In your example
Application.GetOpenFilename("Excel Files (*.xls), *.xls")
will be called twice (one time for the evaluation and one time for the
assignment)
Though you won't recognize a time delay in this short example this can
result in additional running time

2. A second reason is that IMHO the code is easier to read :-)

Frank


Lorne wrote:
Dear Hank,

I am teaching myself VBA by reading this newsgroup and getting
understanding the code you all kindly put on it and can now do quite
a surprising amount of things, but I have a question. Almost always
the code sets something equal to a variable and then tests the
variable rather than simply testing whatever the something is - you
do it in your solution below & so does Frank Kabel in the same
thread. Why don't you use:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address < $A$2" Then Exit Sub
If Application.GetOpenFilename("Excel Files (*.xls), *.xls") Then
Target.Value = Application.GetOpenFilename("Excel Files
(*.xls), *.xls")
End If

End Sub

I am trying to understand if there is a technical reason for the
extra steps of creating the variable vnt and setting it equal to
Application.GetOpenFilename or whether it is just the way everyone is
taught to do it.

Many thanks if you can spare the time help (or if there is a web
resource to answer such questions).



"Hank Scorpio" wrote in message
...
On Tue, 3 Feb 2004 04:46:35 -0600, dreamer
wrote:

Is there a way to make the dialog box which opens a file appear
when a certain cell is selected? For example if cell A2 is
selected, a dialogbox for opening a file asks user which file
should be opened and the writes the name of the file to cell A2.

Right click on the sheet tab and select "View Code", then paste this
into the module that appears:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim vnt As Variant

If Target.Address = "$A$2" Then

vnt = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

If vnt = False Then
Exit Sub
Else
Target.Value = vnt
End If

End If

End Sub

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to
do.)
* Please keep all replies in this Newsgroup. Thanks! *