VBA to force save as macro-enabled workbook

Hello,

I have a macro enabled template workbook and I would like it to force the user to save the file as a macro-enabled workbook.

I have found the following code and copied it into the 'This Workbook' module but it's still saving as a Excel Workbook file.

Please Login or Register  to view this content.

Any help would be greatly appreciated!

Thank you!

Hi there,

See if the following code does what you need:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hey Greg!

Thank you so much for your help!

Just a couple of questions if you wouldn't mind helping.

When the file saves using this code, "xlsm.xlsm" is added to the end of the filename - is there a way to prevent this?

Also, is there a way to write in a line of code to automatically set the file name to today's date in the format "dd.mm.yyyy"?

And lastly, as this code automatically sets it to save as a macro-enabled workbook, I am having trouble saving it as a macro-enabled template in the first instance. The file type drop-down in the save window isn't giving me any other options now. Is there a way to override this code just this first time?

Thank you!

Hi again,

To create and save the template for the first time:

Open a new workbook

Save the (empty) workbook as a template workbook using whatever name is appropriate

Open the TEMPLATE WORKBOOK itself from within Excel - i.e. don't just click on its icon in File Explorer (that would open a "normal" workbook based on the template)

Paste the code from my previous post into the ThisWorkbook VBA CodeModule of the template workbook

Perform a "normal" Save on the template workbook (Use Ctrl+s or the Save icon on the Quick Access Toolbar)

Close the template workbook

Now click on the icon for the template workbook to open a new workbook based on the template and use the SaveAs routine (F12) to have the new workbook saved as a Macro-Enabled workbook named with today's date.


Hope this helps - as always, please keep me informed.

Regards,

Greg M

Hi Greg,

Thanks again! The saving as a template workaround has worked a charm.

However, the code to save with a file name of today's date isn't working annoyingly - the file name remains blank using both SaveAs and Save from the quick access toolbar. Do you know why this might be?

Thank you!

Hi again,

When I click on the template icon to open a new workbook based on the template, and then press F12, I see the Save As dialog box with the "File name" box pre-populated with today's date.

Have I misunderstood your requirements?

Regards,

Greg M

Hey Greg,

No that's exactly what I want it to do so I don't know why its not working.

The code is saved in the correct place - and the default save as macro-enabled workbook is still working.

I'm opening the template file from file explorer and and trying save as as well as save from the quick access.



I'll try on my home PC later and see if I get the same issue.

Thanks!

Hi again,

Try using dashes instead of full stops when specifying the date format:

Please Login or Register  to view this content.

I'm not seeing any error messages at this end.


Keep me posted!

Regards,

Greg M

Still nothing Is it working from your PC?

Hi again,

Yes, I opened the workbook directly from your last post (i.e. I didn't download and then open it), changed the full stops to dashes in the date format constant, and then pressed F12.

The Save As dialog box appeared immediately and the File name box was pre-populated with today's date.

I can't really suggest anything else for the moment - please keep me posted.

Regards,

Greg M

Ah! It's working now (with dashes) - don't know why it didn't before. Thanks for all your help

Hi again,

Thanks for all of your feedback - glad to hear that things are working correctly now.

You're welcome - glad I was able to help.

Regards,

Greg M