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.

application.EnableEvents = False
' open another workbook
Application.EnableEvents = True

will stop the workbook_open event from firing if that is all you are trying
to do.

--
Regards,
Tom Ogilvy




"Busy Bee" wrote:

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.

You can store variables available to all workbooks in all sorts of places,
eg cells in some workbook or addin, registry (see SaveSetting & related 'see
also' functions), set references between projects.. Another way below, which
seems closest to the approach you want to use -

' in a normal module in the 'main' workbook

Private collationWB As String ' used to identify this workbook
Private collectingData As String

Public Function GetString(sName As String) As String
Dim sRet As String
Select Case sName
Case "collationWB": sRet = collationWB
Case "collectingData": sRet = collectingData
Case Else: sRet = ""
End Select
GetString = sRet
End Function

Public Function LetString(sName As String, sValue As String) As Boolean
LetGlobalString = True
Select Case sName
Case "collationWB": collationWB = sValue
Case "collectingData": collectingData = sValue
Case Else: LetGlobalString = False
End Select
End Function

' in other workbooks

Sub test1()
Dim collationWB As String
Dim sReturn As String

'' CHANGE "Personal.xls" to name of 'main' workbook
Call Application.Run("Personal.xls!LetString", "collationWB", "ABC.xls")

sReturn = Application.Run("Personal.xls!GetString", "collationWB")

collationWB = sReturn
MsgBox collationWB

End Sub

I didn't study what you are doing but from what I read I'd be tempted to
store the variables in cells in a hidden sheet in your 'data collection
workbook'.

Regards,
Peter T

"Busy Bee" <Busy wrote in message
...
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.

Thanks, that seems a much easier way of achieving the goal.

"Tom Ogilvy" wrote:

application.EnableEvents = False
' open another workbook
Application.EnableEvents = True

will stop the workbook_open event from firing if that is all you are trying
to do.

--
Regards,
Tom Ogilvy




"Busy Bee" wrote:

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.