Working with a template - referencing and activating

[XP]

Using Office 2003 and Windows XP;

I am opening a file as a template (Workbooks.Add) to keep users from
damaging a form that is opened from a button on a toolbar.

However, because the template is unsaved, it's a bit harder to work with.
For example: Workbooks("TemplateName").Activate
The above didn't work for me to switch back and forth between files.

Is there another way to reference an unsaved template file so I can activate
it when needed?

Just personal preference, but I'd rather not use an object reference when it
is added, however, I will if it is necessary...if this is the only way, could
someone please post example code on how to get the object reference? --- and
then, does the object reference stay available even though the file loses
focus?

Thanks much in advance.

 

[Chip Pearson]

Use code something like

Sub AAA()
Dim WB As Workbook
Set WB = Workbooks.Open(Filename:="C:\MyTemplate.xlt")
End Sub

Then you can do anything you want with the WB object within the sub AAA.
Because WB is declared within the AAA procedure, it will be destroyed when
the procedure ends. If you declare it as a Public variable, before and
outside of any procedure, it will remain accessible until the workbook in
which it is defined is closed. A Public variable is accessible in any
procedure in any module of the VBA Project.

Public WB As Workbook

Sub AAA()
Set WB = Workbooks.Open(filename:="C:\MyTemplate.xlt")
End Sub

Sub BBB()
MsgBox WB.Name
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)