Problem with Public Variable in VBA (Excel97)

[Guest_imported]

I'm having a rough time with a public variable. Any light you can shed on this matter is greatly appreciated.

Here's what's happening (in Excel 97)


I declare a public variable in "Workbook1.xls"

A sheet is copied from "Workbook1.xls" to "Workbook2.xls"

Included in this sheet is a Worksheet_Change Procedure which refers to the public variable.

Every time the worksheet in "Workbook2.xls" is changed the public variable is empty.
(Even when the public variable still has a value indicated in my watch expression)

Please note that the change(s) in "Workbook2.xls" always occur while the macro from "Workbook1.xls" is still running.

My project does not require variable declaration.

What I need is a way to access the value of the Public variable in "Worksheet1.xls"

Thanks again,

Matthew

 

[cLocKwOrkPRO]

Can't you just do something like:

GimmeTheValue = Workbooks(&quot;Workbook1.xls&quot.Worksheet(&quot;<SheetName>&quot.yourPublicVariable

?

U might have to tinker with it.. like sometimes it has to be Workbook instead of Workbooks... this is off the top of my head. Basically u need to get to the sheet object in your workbook.