If you put that
Public strPath as String
In a General module--not behind a worksheet, you'll be done.
It'll be able to be seen anywhere.
I think I'd be careful, though.
I put this all in a general module:
Public strPath As String
Sub Get_Folder()
strPath = InputBox(prompt:="Enter Entire Path for Folder Location!")
End Sub
Then in any/all procedures that need that string:
if strPath = "" then
call Get_Folder
end if
====
As a user, I'd hate to type anything! And as a developer, I'd hate to validate
what the user typed--and tell them to retype the whole string if it was wrong.
Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)
John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm
If you and all your users are running xl2002+, take a look at VBA's help for:
application.filedialog(msoFileDialogFolderPicker)
Lance Hoffmeyer wrote:
Hey all,
Let me preface this by stating that I am not a programmer and know
just enough to be dangerous.
I have been writing various subfunctions within each sheet of a XL workbook.
I have a line of code to get the folder path because although the file names I am
using from month to month will remain the same the folder path will change.
Each time I click on a control button on the XL sheet: Private Sub CommandButton1_Click()
this line is run:
strPath = InputBox(prompt:="Enter Entire Path for Folder Location!")
What do I need to do so that I run this only once (say on the first sheet) and this variable
is then passed to all the other sheets as I run them?
My thought was to put this in a module:
Public strPath As String
Sub Get_Folder
strPath = InputBox(prompt:="Enter Entire Path for Folder Location!")
End Sub
How do I then pass this to a sheet subfunction?
Sub Sheet1_Run()
End Sub
Thanks in advance,
Lance
--
Dave Peterson