Hi

I have a set of workbooks that are normally used by users. I also run some
code from another workbook to collect data from these other workbooks. When
this happens I do not want some of the code in the user workbooks to run.

I set up a public variable in a standard module in the collecting workbook,
and set it to a value before opening the workbooks I am collecting the data
from. No matter what I do (having read many Q&A from this discussion group),
the code in the user workbook will not accept the line referring to the
public variable.

Some sample code (not the working code as now I am just trying to get a
string out in msgbox) is below.
in data collection workbook
[qoute]
Option Explicit
Option Compare Text
Public collationWB As String ' used to identify this workbook
Public collectingData As String

Sub collectData()
collectingData = "some words of hope"
[unquote]

and in user workbook (again this is just current failing example)
[quote]
Private Sub Workbook_Open()
'If Workbooks("unitCompletion.xls").collectingData Then
MsgBox Workbooks("unitCompletion.xls").CollectCode.collec tingData
[unquote]
As you can tell, this is in ThisWorkbook module

Any help appreciated.