Set default Save-As parameter to Macro Enabled workbook

I've tried a couple solutions that I found elsewhere, namely at the link below but the odd thing is that although the Save-As dialog shows the macro enabled save option selected (in fact, it's the only option available with this code) the resultant error message is the same as if I tried to save a macro enabled worksheet as a standard spreadsheet...

"The following features cannot be saved in macro-free workbooks...to continue saving as a macro-free workbook click yes"

Is there some more recent code that allows me to set the default Save-as to a .xlsm file and not error out in the process?


http://www.ozgrid.com/forum/showthread.php?t=147820

I'm working with that link you sent (thank you, BTW) but I'm having an issue where the file name seems to always default back to the temporary/example file name I've provided, even if the file has been saved to another name. Is there some way to run this code snippet only when the file has never been saved?

ActiveWorkbook.SaveAs "Underwriting (property name).xlsm", FileFormat:=52

Thanks, but I'm still struggling with this. What I'd like to do is offer the user an example file name, "Underwriting (property name).xlsm", but not actually save to that name. I want the users to edit the "property name" portion to the name of their choosing.

What is happening is that the file gets saved immediately during the save or save-as dialog and because of this the user is constantly prompted about saving to an existing file name.

I've placed this code in the "BeforeSave" of the Workbook. Should I be placing it somewhere else?

This was close but I'm finding that anything I do to try and influence the process, there's always some way to muck it up. The first time I ran with this code I could not get out of the loop of providing a partial name. Save and Save-As both seem to have different results, and Cancel did not always cancel. Also, for some reason, whenever I attempted to save, it kept defaulting to the template directory even though I selected a different one.

Ok, yes, I wasn't very clear before…and thanks for helping

I created a template file that contains macros and as a result, it is saved as a Macro-enabled template file, one that ends in .xltm. When this file is placed my template directory it becomes available via the File/New menu. When selected, the default file name is the same as the original with the number “1” appended to the end, but the default file type is .xlsx, a non-macro enabled file. Because the file has macros in it, and because some users are just not able to grasp what the error message tells them about saving as macro-enabled, I wanted to set the type for them. I also wanted to remove the number “1” from the file name as well.
In addition, the default name I’d like to present is Underwriting ().xlsm so that they can enter a property name between the parenthesis. The code I’ve tried so far basically saves the file immediately with the default name but without giving the user an opportunity to edit the name and place anything between the parentheses. Then, when they try and save the file a second time, the user is presented with a “file name already exists” error message since the code just saves, but does not stop and allow the user to set the name.
I’d like to stop at the point where the file name is provided but the file is not immediately saved.

Wow, thanks...This works except under one condition. If I select Save or Save-As but then cancel, the default file name changes to False.xlsm and the file is saved anyway (the "save" action is never really cancelled). This in itself is okay the first time however, when it saves the file, it will overwrite an older file if it exists with the same name but without a warning about doing so or an option to stop it. This overwrite problem occurs even when you don't cancel and respond to the naming prompt and set the file name to something that already exists. This will overwrite the first one without warning.

There's one other thing I noticed. The template defaults to the "My Documents" folder, even if I selected another folder in the Save/Save-As dialog. Just an observation, not really a problem, but it took me awhile to figure out what was occurring when my files didn't appear where I expected them.

What I'm seeing here is that this is a very complicated operation, especially when you add in a file name template as well as a file type. I really appreciate the effort, this was asking a lot and this does deliver. Given that, maybe it'll be simpler to set the file type for the users so that they don't have to be prompted with the macro-enabled file type message? Then they can set the file name to whatever they want, but be forced into a macro-enabled file type.

Thanks to both HaHoBe for the source of this code and AlphaFrog for providing the change to this solution that completed this as a functional solution, shown below:

Please Login or Register  to view this content.

Greetings All,
I know this is the Excel forum but as this is the closest thing I have found and is VB related I will venture out on a limb and Ask. Is it possible to use this code and it perform the same function in Word 2010. If so what would I need to change. I would appreciate any help in the matter as this is the same situation I am facing with needing to force the default save as a* . Docm file to enable the use of Macros . Excel VB I can usually find something close enough online modify it and it works but trying this in word .... Well I am at loss and need some guidance.
Thank You in Advance.