Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Just wondering
G'day there One and All,
I have a workbook with 4 worksheets. In a general code module I've declared 4 Public variables thusly: Public aSht as Worksheet Public sSht... etc. In my code I then use Set to assign each worksheet to its variable. Set aSht = Worksheets("Analysis") This works fine, however I've found that since I have no control over which section of the code will run first (determined by the user's options) I've had to assign the variables more than once, at the start of several different routines. Is there anywhere I can put the assignments where I need only use them once? I thought of the Workbook_Open() section of ThisWorkbook, but that's not going to work as it runs before the sheets are loaded. Any ideas? It's not a biggy, as my code runs successfully and this may just have to be. I just thought there may be a more elegant & efficient way to assign them. See ya Ken McLennan Qld, Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Just wondering
In a general module, not the thisworkbook module, put in your public
variables then in the ThisworkbookModule Private Sub workbook_open() set aSht = thisworkbook.Worksheets("Sheet1") set sSht = Thisworkbook.Worksheets("Sheet2") etc. end Sub It should work. -- Regards, Tom Ogilvy "Ken McLennan" wrote in message .. . G'day there One and All, I have a workbook with 4 worksheets. In a general code module I've declared 4 Public variables thusly: Public aSht as Worksheet Public sSht... etc. In my code I then use Set to assign each worksheet to its variable. Set aSht = Worksheets("Analysis") This works fine, however I've found that since I have no control over which section of the code will run first (determined by the user's options) I've had to assign the variables more than once, at the start of several different routines. Is there anywhere I can put the assignments where I need only use them once? I thought of the Workbook_Open() section of ThisWorkbook, but that's not going to work as it runs before the sheets are loaded. Any ideas? It's not a biggy, as my code runs successfully and this may just have to be. I just thought there may be a more elegant & efficient way to assign them. See ya Ken McLennan Qld, Australia |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Just wondering
G'day there Tom,
In a general module, not the thisworkbook module, put in your public variables then in the ThisworkbookModule Private Sub workbook_open() set aSht = thisworkbook.Worksheets("Sheet1") set sSht = Thisworkbook.Worksheets("Sheet2") etc. end Sub It should work. Yuppers!! It worked like a charm. Thanks for your advice, Tom. Much appreciated. See ya Ken McLennan Qld, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
We were wondering how to edit an array once it has been set up? | Excel Worksheet Functions | |||
Nothing to do with Excel....wondering if there is a way to check.. | Excel Discussion (Misc queries) | |||
I am wondering if someone could help me solve this equation | Excel Discussion (Misc queries) | |||
Wondering if there is a way? | Excel Programming | |||
Wondering If Possible | Excel Programming |