Passing Public Variable into Sheet Sub Functions

[Lance Hoffmeyer]

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]

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)

 

[Bob Phillips]

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

If strPath is declared in a standard code module, just use that variable
within your other procedures.

If it is declared within a worksheet code module, precede it by the sheet
codename, such as Sheet1.strPath.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)