Force user to save with xlsm extension?

[TheWennerWoman]

Hello, we've had several instance lately where users are opening a macro-enabled spreadsheet, completing it and then saving as with an xlsx extension.

Even though Microsoft pops up a message warning them that they'll lose macros, users are still going ahead.

is there a couple of lines of code I can put somewhere that simply stops them from saving with anything other than an xlsm extension?

Thanks for reading.

 

[Kerryx]

Did you try this?

Force Save As Macro Enabled Workbook Only Problem

First, I want to say thank you to everyone who contributes to this website. I am new to working with macros and VBA – Self taught by reading and using codes found on this site so bear with me if I use any wrong terminology in describing my problem below. I am running Excel 2016 on a Windows 7...

www.mrexcel.com

 

[TheWennerWoman]

Did you try this?

Force Save As Macro Enabled Workbook Only Problem

First, I want to say thank you to everyone who contributes to this website. I am new to working with macros and VBA – Self taught by reading and using codes found on this site so bear with me if I use any wrong terminology in describing my problem below. I am running Excel 2016 on a Windows 7...

www.mrexcel.com

yes - it doesn't work.

If I try to save my xlsm file as an xlsx file I simply get the standard Microsoft warning about "the following features cannot be saved in macro-free workbooks".

 

[TheWennerWoman]

yes - it doesn't work.

If I try to save my xlsm file as an xlsx file I simply get the standard Microsoft warning about "the following features cannot be saved in macro-free workbooks".

Ok, I managed to find this piece of code from this site around 9 years ago which does the trick

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim txtFileName As String
' Check if user has selected Save As
If SaveAsUI = True Then
Cancel = True
' Call modified dialog box. Cancel if user selects Cancels in the dialog box.
txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file", "Excel Macro-Enabled Template (*.xltm), *.xltm"))
If txtFileName = "False" Then
MsgBox "Action Cancelled", vbOKOnly
Cancel = True
Exit Sub
End If
' file is saved as XLSM macro-enabled file
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
End If
End Sub