file browser when a cell in selected
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 |
file browser when a cell in selected
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! * |
file browser when a cell in selected
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/ |
file browser when a cell in selected
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/ |
file browser when a cell in selected
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 |
file browser when a cell in selected
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! * |
file browser when a cell in selected
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! * |
file browser when a cell in selected
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! * |
file browser when a cell in selected
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 |
file browser when a cell in selected
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! * |
All times are GMT +1. The time now is 06:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com