Force save file opened as xltm to xlsm

inquisitor

New Member
Joined
Jan 15, 2012
Messages
15
I've seen various code snippets that purport to force a file to save as xltm from xslm, but I can't make them work. I wonder if I'm attaching them properly to the file? If you read that and think "those words don't even make sense together," then you've got a good handle on my level of understanding.

I'm using Excel for Mac (16.35). I'm going to "view code" after right-clicking on the tab I'm on, selecting ThisWorkbook and pasting any number of code snippets that *look* like they should work, but don't. When I exit the editor and hit Cmd-S, I get the save-as prompt and xlsx is selected, not xlsm. How do I correct this?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

inquisitor

New Member
Joined
Jan 15, 2012
Messages
15
I keep getting close but not quite there ... I'm using this for reference:

I'm noticing if I hit Cmd-S, it doesn't trigger the macro. If I choose File->Save As... I get an error box saying Runtime Error 1004 - Method 'GetSaveAsFilename' of object '_Application' failed.


Code I'm using:

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")
txtFileName = Application.GetSaveAsFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm, Excel Macro-Enabled Template (*.xltm), *.xltm", Title:="Save As...")
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

What am I doing wrong?
 

Forum statistics

Threads
1,144,630
Messages
5,725,394
Members
422,623
Latest member
Dave52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top