Excel Template Files

[JimDandy]

When you create a new document based on a Macro-Enabled Template (*.xltm), shouldn't the file it creates have the default file type of a Macro-Enabled Workbook (*.xlsm) instead of a standard Excel Workbook (*.xlsx)? So many of the users don't respond properly to the prompt when saving that when they open the file later, they've lost all the functionality.

I've tried using VBA to set the file type in various ways thanks to the helpful suggestions from this group, and as long as the user doesn't cancel out of the dialog or mess things up, it works fine but selecting Cancel, for instance, during the save-as causes the worksheet to save itself anyway with "FALSE.xlsm" as the file name....it does get the correct file type, but I cannot have all my files trying to save to that name.

[AlphaFrog]

I've tried using VBA to set the file type in various ways thanks to the helpful suggestions from this group, and as long as the user doesn't cancel out of the dialog or mess things up, it works fine but selecting Cancel, for instance, during the save-as causes the worksheet to save itself anyway with "FALSE.xlsm" as the file name....it does get the correct file type, but I cannot have all my files trying to save to that name.

Can you show your code?

[JimDandy]

This is code I received from another forum member, it's in ThisWorkbook object under BeforeSave:

Please Login or Register  to view this content.

It performs "okay" as long as you don't change your mind midway through saving.

I've tried various methods to change the file type during save, and none of them are without some sort of failure if you step outside the norm. My concern is that as a Macro-Enabled Template, the result file should default to Macro-Enabled Workbook out of the box too without having to resort to using VBA code to accomplish this. It may be that I'm missing a setting somewhere...

[AlphaFrog]

Just move the one line to where it is below. If they cancel, the file is not saved.

Please Login or Register  to view this content.

[JimDandy]

Ok, that was great...thank you!

[jwlamb]

Moved to new thread

[AlphaFrog]

It doesn't have anything to do with the message box. You would test if the File Name returned from .GetSaveAsFilename prompt equals False to determine if they canceled that prompt.

This site has some good code examples
Use VBA SaveAs

Please Login or Register  to view this content.