Background....
I have a requirement to keep a firm grip on issue control of
excel macros within my company. All macros must display their
name and issue on end users worksheet(s). The model we want
to adopt is one of having a central location for all our excel
workbook macros controlled by a librarian whose job includes
managing the updates (booking the macros out of and back into
the central location). In at least one project end users are
provided with a workbook whose macro does little more than
reference the central location. Results are written back to
the end user's workbook making the process completely
transparent to the end user (most think they have a copy of
the macro source code - ho,ho). So I know that they will
always be using the most up-to-date macro as opposed to one
that may have been tucked away or donated by a "kind" friend.
Any copying they do will only be to copy the end user workbook
which will remain "hooked up" to the central location. Neat hun!!
Well I think so.

It works....sort of!!
OK. So what I have is an end user workbook(A) with a few VBA
lines one of which is to open workbook(B) in the central location.
As soon as workbook B opens it runs the carefully controlled VBA
routines using sub workbook_open. No problem so far but (and there
had to be one) during testing it became clear that the end user's
workbook (workbook A) is not necessarily opened as workbook 1.
Some users have personal.xls in their startup folder. Arghhh!!
Likewise workbook B may not be workbook 2. Each workbook when
active knows its own name and number, but is seemingly incapable
of communicating this (via public variables or routine parameters)
to an other. For our controlled workbook B to present the results
in end users workbook A, it has to know either workbook A's number
(preferred) or it's name. Currently it just guesses that workbook
A has a workbook number one less than Workbook B! I consider this
a risky assumption hence the reason for this note.

The question.
So the question is thus, how does the macro in one workbook
communicate it's variables (values) to the macro in another open
workbook? I actually only want one integer value (the workbook A
number) to be available to workbook B.
Any help would be much appreciated.

"MAJOR TOM" wrote:

Background....
I have a requirement to keep a firm grip on issue control of
excel macros within my company. All macros must display their
name and issue on end users worksheet(s). The model we want
to adopt is one of having a central location for all our excel
workbook macros controlled by a librarian whose job includes
managing the updates (booking the macros out of and back into
the central location). In at least one project end users are
provided with a workbook whose macro does little more than
reference the central location. Results are written back to
the end user's workbook making the process completely
transparent to the end user (most think they have a copy of
the macro source code - ho,ho). So I know that they will
always be using the most up-to-date macro as opposed to one
that may have been tucked away or donated by a "kind" friend.
Any copying they do will only be to copy the end user workbook
which will remain "hooked up" to the central location. Neat hun!!
Well I think so.

It works....sort of!!
OK. So what I have is an end user workbook(A) with a few VBA
lines one of which is to open workbook(B) in the central location.
As soon as workbook B opens it runs the carefully controlled VBA
routines using sub workbook_open. No problem so far but (and there
had to be one) during testing it became clear that the end user's
workbook (workbook A) is not necessarily opened as workbook 1.
Some users have personal.xls in their startup folder. Arghhh!!
Likewise workbook B may not be workbook 2. Each workbook when
active knows its own name and number, but is seemingly incapable
of communicating this (via public variables or routine parameters)
to an other. For our controlled workbook B to present the results
in end users workbook A, it has to know either workbook A's number
(preferred) or it's name. Currently it just guesses that workbook
A has a workbook number one less than Workbook B! I consider this
a risky assumption hence the reason for this note.

The question.
So the question is thus, how does the macro in one workbook
communicate it's variables (values) to the macro in another open
workbook? I actually only want one integer value (the workbook A
number) to be available to workbook B.
Any help would be much appreciated.

I have found a solution from another source which does the trick. Seed a
password in an unused cell in workbook A. Hide it and if necessary lock the
cell. I put my car reg no under a control button. Workbook B cycles through
all open workbooks until it finds the password in the known cell position.
Voila, the workbook A number is now known to workbook B.