Remove/disable event macros in SavedAs wkbk?

[Ed]

I have a "master template" workbook. When the user fills in certain
information, the workbook generates a name for itself and performs a SaveAs
NewFileName.

The master has Open, Close, and Worksheet_Change events that are not needed
in the new workbooks. Is there an easy way to remove or disable these event
codes when the new workbook is created by SaveAs?

Ed

 

[Guest]

If you don't want to edit the VBA directly when you save it by removing the
uneeded modules, you could put an if statement in each event that checks the
name of the workbook, if it is not the "master template" then do not run.
ben

 

[Dave Peterson]

Chip Pearson has some instructions on how to remove code on the fly.
http://www.cpearson.com/excel/vbe.htm

But I like Ben's suggestion, too.

If the template is really a template (*.xlt) and the workbook that's created is
really a new workbook based on that template, you can check the path of that
workbook. If it's = "", then the workbook hasn't been saved and you can do your
stuff.

option explicit
sub workbook_open()
if me.path <> "" then
exit sub
end if

'keep going
end sub

 

[Ed]

Thanks, Ben and Dave. I've got the IF in there, based on the workbook name,
but it seemed there might be a better way. The file is not a .xlt; just a
..xls that is opened, written to via UserForms, and SavedAs. I might check
out the tips from Chip Pearson - I might be able to remove the code before
the save so the new workbook doesn't have it.

Dave - why would a .xlt file have no path? Doesn't it have to exist
somewhere?

Ed

 

[Guest]

Ed,

What he meant is that if it is NOT an .xlt then if it has been saved
then it will have a pathname. IF it has not been saved there will be NO
path name. It is the *.xls that has a possiblilty of no pathname, having been
opened off of an .xlt

Thanks, Ben and Dave. I've got the IF in there, based on the workbook name,
but it seemed there might be a better way. The file is not a .xlt; just a
..xls that is opened, written to via UserForms, and SavedAs. I might check
out the tips from Chip Pearson - I might be able to remove the code before
the save so the new workbook doesn't have it.

Dave - why would a .xlt file have no path? Doesn't it have to exist
somewhere?

Ed

 

[Ed]

Got it. Thanks for clearing up my confusion.
Ed

Ed,

What he meant is that if it is NOT an .xlt then if it has been saved
then it will have a pathname. IF it has not been saved there will be NO
path name. It is the *.xls that has a possiblilty of no pathname, having been
opened off of an .xlt

 

[Dave Peterson]

If you really create a new workbook based on a .xlt file, then it won't have a
path.

Just like clicking on the New Icon on the standard toolbar creates a workbook
called book#. It doesn't have an extension and doesn't have a path.

If you open the template with File|open, then you'll be really opening the
template to edit it--not as a basis for a new workbook.

File|New then pointing to that template file or double clicking on the *.xlt
file in windows explorer should create a new workbook based on that template.

 

[Ed]

Thanks for the explanation, Dave. I've never explored .xlt files. I guess
I throw around the label "template" too easily - to me, it's just a
boiler-plate file that gets filled in and saved. But there's probably some
aspects of functionality that I'm missing by not using the .xlt format.
I'll have to study that.

Ed