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

--
Message posted from http://www.ExcelForum.com

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! *

Try to put following code into SelectionChange Event of your worksheet
:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("A1").Address Then Range("A1") =
Application.GetOpenFilename
End Sub

P.S : Not tested


---
Message posted from http://www.ExcelForum.com/

Hi
you can use the worksheet_selectionchange event. Put the following code
in your worksheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Filename
If Target.Address < "$A$1" Then Exit Sub
Filename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If Filename = False Then Exit Sub
Target.Value = Filename
End Sub

HTH
Frank





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.


---
Message posted from http://www.ExcelForum.com/

Hank Scorpio wrote:
*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 whe
a
certain cell is selected? For example if cell A2 is selected, a
dialogbox for opening a file asks user which file should be opene
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 t
do.)
* Please keep all replies in this Newsgroup. Thanks! * *


This works great. Thanx

--
Message posted from http://www.ExcelForum.com

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! *

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! *

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! *

Tom Ogilvy wrote:
Since application.GetOpenFilename shows the file open dialog, I
believe showing it twice would be noticeable.
lol
forgot this "minor" issue. Thanks for adding it :-)
Frank

And what would happen if the user chose a name the first time, but clicked
cancel the second?



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! *

--

Dave Peterson

On Tue, 3 Feb 2004 14:22:29 -0000, "Lorne"
wrote:

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

As Tom pointed out in a later message, the code that you have above
will call the dialog twice and (as Dave also pointed out) there's no
guarantee that the user will make the same selection on both
occasions.

Generally speaking, though, the question of whether to assign a value
to a variable or to use it directly just depends on circumstances and,
to some extent, a person's particular style.

In some cases a value returned by a function or method will need to be
used more than once. In such cases it obviously makes sense to assign
it to a variable. In others, the return value may be of different data
types depending on the input. That's the case with the
Application.GetOpenFilename method. A valid assignment will return a
string of text. Cancelling the dialog, however, will return a logical
value of False. While I COULD have done a direct assignment like:

Target.Value = _
Application.GetOpenFilename("Excel Files (*.xls),*.xls")

then if the user cancelled the dialog the value False would end up
being stored in the cell A2. I assumed that the developer and user
wouldn't want that to happen, so I only put the value into the cell if
the value returned by the method is NOT False.

Another reason can be that some of the return values of a function or
method may not be compatible with your following line(s) of code. In
such a case, it makes sense to test the returned value first to
prevent a type mismatch or similar error from occurring. This is
particularly so when a function or method returns a variant value,
which, as we've already seen, could be any data type depending on the
circumstances.

And in some cases, assigning to a variable just makes the code easier
to read and more self documenting.

There isn't always One Right Way, but these are some of the factors
that can help determine the choice of approach.

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).

There are quite a few web resources, such as:
http://j-walk.com/ss/
and
http://www.cpearson.com/excel.htm

but for specific questions like this one it's probably faster to just
ask it in the Usenet group.

"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! *