Force users to save as .xlsm or xltm only

[Mel81]

Hello,
I have a macro which forces users to save the excel file as macro-enabled file (.xlsm) and I want to add macro-enabled template (.xltm). I just can't get it to work. I have tried to add .xltm as you can see in code below. I have also included the working .xlsm line in green, as that worked.

Can anybody help me, I've been stuck on this for 2 days. Thankyou

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(, "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

 

[adam087]

Hello! Welcome to the board

Try replacing the offending line with this one...

txtFileName = Application.GetSaveAsFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm, Excel Macro-Enabled Template (*.xltm), *.xltm", Title:="Save As...")

Hope that helps.

/AJ

 

[Mel81]

Hi AJ,

That's worked perfectly. Thankyou very much for your help.

 

[adam087]

No probs. Thanks for your feedback.

/AJ