Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Public Variable into Sheet Sub Functions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Public Variable into Sheet Sub Functions
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Public Variable into Sheet Sub Functions
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Lance Hoffmeyer" wrote in message news:XtShj.48$cz3.11@trnddc06... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Public Variable into Sheet Sub Functions
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) "Lance Hoffmeyer" wrote in message news:XtShj.48$cz3.11@trnddc06... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
passing a variable from sheet to form to another sheet | Excel Programming | |||
public function having array passing out to a sub dropdown | Excel Programming | |||
Passing Variable from Sheet to Userform | Excel Programming | |||
Use of public variables vs. passing arguments | Excel Programming | |||
Passing Public Module Vairable to a Worksheet Event | Excel Programming |