Use of global between workbooks

[Guest]

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)

Private Sub Workbook_Open()
'If Workbooks("unitCompletion.xls").collectingData Then
MsgBox Workbooks("unitCompletion.xls").CollectCode.collectingData
[unquote]
As you can tell, this is in ThisWorkbook module

Any help appreciated.

 

[Guest]

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

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)

Private Sub Workbook_Open()
'If Workbooks("unitCompletion.xls").collectingData Then
MsgBox Workbooks("unitCompletion.xls").CollectCode.collectingData
[unquote]
As you can tell, this is in ThisWorkbook module

Any help appreciated.

 

[Peter T]

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

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)

Private Sub Workbook_Open()
'If Workbooks("unitCompletion.xls").collectingData Then
MsgBox Workbooks("unitCompletion.xls").CollectCode.collectingData
[unquote]
As you can tell, this is in ThisWorkbook module

Any help appreciated.

 

[Guest]

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

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

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)

Private Sub Workbook_Open()
'If Workbooks("unitCompletion.xls").collectingData Then
MsgBox Workbooks("unitCompletion.xls").CollectCode.collectingData
[unquote]
As you can tell, this is in ThisWorkbook module

Any help appreciated.