Question about scoping variables

[TBA]

Excel 2000
Windows 2k Pro

I'll try to be brief:

I have a workbook that when it opens closes all other open workbooks. Not
very flexible, I know, but down the line I'll add the appropriate warnings
and whistles. Anyway, once in this workbook the user may be prompted to
open another workbook so that they can copy and paste data from that
workbook to the original workbook. There are (at the time of this writing)
three standard modules and three userforms involved also, in addition to
some code in the ThisWorkbook module.

What I'm having a hard time with is being able to keep track of which
workbook is which, and it may be that I need to activate or select a
workbook from either a standard module or a userform module. I know how to
set a variable equal to the active workbook, but often times the userform
module won't recognize it or it will work once then not again after that. I
know this must be a variable scope issue, but I'm confused on where the
variables should be declared. I know that Public variables must be declared
in a standard module, but I'm having limited success with that.

For arguments sake the workbook variables are MyWB and TempWB, with MyWB
being the original, and there will never be more than two workbooks open at
a time. So, what is the best way to declare these workbook variables so
that ANY module can access them? Or have I bitten off more than I can chew?

All hints and advice greatly appreciated.

-gk-

 

[Tom Ogilvy]

in a standard module (as you say)

Public MyWB as Workbook
Publc TempWB as Workbook

Sub OpenBook()
sStr = "C:\My Documents\MyFile.xls"
set TempWb = Workbooks.Open(sStr)
set MyWB = Thisworkbook ' workbook containing the code
End Sub

You have to set the variables so they have values.

Make sure you don't have any plain END statements in your code. This resets
variables. Don't hit the reset button in the VBE. This clears you
variables as well.