assign macro - toolbar

[bryan]

I am re-asking this as I'm not getting any info back and project is needed.

I have a protected workbook template named Reinsurance.xlt which I assign a
macro to toolbar button, macro is called 'SaveToDrive'.
I created the toolbar and then used Commands, Macros, Cutom Button. Right
click on button to assign macro ' SaveToDrive'.

Macro saves as entry in cell A12.
Lets's say I enter 'BJS1' into this cell, it saves as BJS1.xls.
If I open the template and look at the assign macro,
it now says 'c:\BJS1.xls' ! SaveToDrive
not 'Reinsurance.xlt' !SaveToDrive.

The template save is written like this:
sFilename = "c:\" + strcell"
ActiveWorkbook.SaveAs Filename:=sFilename, _
FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.Quit
Application.StatusBar = "Application Closing."

Am I missing something on the save or am I doing something wrong in
assigning the macro?

I've used this same method in Word to save files and the macro never changes.


Thanks,
Bryan

 

[Bernie Deitrick]

When you do a SaveAs, Excel updates the reference to the macro. If you want to keep the reference
to the .xlt, then use

ActiveWorkbook.SaveCopyAs Filename:=sFilename, _ .....

instead.

But a better solution would be to either store the code in your personal.xls or use an add-in, with
additional code to make sure that only workbooks based on the specific template are saved using that
code.

HTH,
Bernie
MS Excel MVP

 

[bryan]

Thank you very much - works as expected.
The only thing I needed to change was using only:
ActiveWorkbook.SaveCopyAs Filename:=sFilename

I initially had the rest of the code in here but, compile error on
Fileformat:=

Is personal.xls like normal.dot in Word?

The macro will only be on certain xlt workbooks....

Thanks,
Bryan