Passing calling workbook name to called workbook

[Guest]

I have two workbooks - I'll call them Main and Compliance. Compliance is used
solely to print the legal compliance stuff. Main handles the concept stuff
and printing the concept stuff.

My custom menu in Main calls a print macro in Compliance. When that macro is
done, I want to activate the Main workbook. The problem is the Main Workbook
name can be different so I can't hard code the name into Compliance code to
activate the Main workbook.

The end users want to save each Main workbook under a different name to
preserve their cases.

How can I send or carry over the name of the Main workbook with what ever is
its current name to the Compliance workbook?

 

[Guest]

You could always have it loop through the workbooks collection and examine
each workbook to find a unique identifying characteristic.

Assume only the main workbook will have a defined name "Main"

Dim bk as workbook, nm as Name
for each bk in application.workbooks
on error resume next
set nm = bk.Names("Main")
On error goto 0
if not nm is nothing then
bk.activate
end if
Next

 

[Guest]

My problem is they renamed the Main workbook to "Watson". Within "Watson" can
I set a variable to the workbook's name and pass that value to the Compliance
workbook?

 

[Ivan Raiminius]

Hi,

basically, you can have
1)public variable with workbook name
2)pass the name as one of arguments while calling your printing
procedure

Regards,
Ivan

 

[Guest]

You said:

My custom menu in Main calls a print macro in Compliance.

Is this a commandbar button?

What do you mean by custom menu

what do you mean by calls a print macro in compliance

A public variable in Main will not be visible in Compliance unless you set a
reference from Compliance to main, so I don't know what Ivan is suggesting
there. I don't see a reference as being feasible in the situation you
describe.

Passing an argument is not really supported if this is a commandbar button.
If you are calling a procedure in Compliance from a procedure in Main using
application.run, then you could pass an argument, but your description didn't
seem to indicate this. Again, your participation in describing the
particulars is applicable.

So maybe if you provide some more information you can get a suggestion that
is compatible with your situation.

The suggestion I provided is applicable in any imaginable scenario by the
way.

--
Regards,
Tom Ogilvy

 

[Ivan Raiminius]

Hi Tom,

Sorry I wasn't clear.

I meant that public variable in Compliance (which is always the same
workbook) may be visible to Main (with reference to Compliance) and
that it is possible to assign value to it from Main.

Regards,
Ivan

 

[Chip Pearson]

You don't need to pass the workbook name. Give your Main project
a new Project Name (in VBA, with the Main project active, go to
the Tools menu, choose "VBAProject Properties" and give the
project a new name like MainProject). Then, in the Compliance
workbook code, use

Application.VBE.VBProjects("MainProject").VBComponents("ThisWorkbook").Activate


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

I had to turn on "Trust access to Visual Basic Project " and then it worked.