Force Save as XLSM when saving file structure

Question

So, I am working with an XLTM file, and I want the user to make sure that they are saved as XLSM. When they click "Save", it works fine, but I find when they click "Save As", the file is saved as "* .xlsm.xlsm". I lost a little how to make sure the user saves as XLSM, saving the file name as "filename.xlsm" and not "filename.xlsm.xlsm".

'Action makes sure the user saves as XLSM file type. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FileNameVal As String If SaveAsUI Then FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm") Cancel = True If FileNameVal = "False" Then 'User pressed cancel Exit Sub End If Application.EnableEvents = False ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat Application.EnableEvents = True End If End Sub 

I thought the problem might have been spelling ".xlsm" in:

 ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=ThisWorkbook.FileFormat 

However, without the ".xlsm" written there, I believe that the file instead saves as the suffix of the file. (For example, if my XLTM file is called Template (File001) .xltm, and the user opens a new template file, he will save (File001) 1 as a template (assuming that "1" 1 "is a file type).

It may be the structure of my code, so I need guidance on how to revise it.

Answer 1

The problem seemed to exist because the template would name the file "Template (1) 1" before it was actually saved initially. This changes the way the Excel file is saved, so the easiest way to contrast this initial save and the subsequent save (which already contains the file extension) was to use an if-then statement to determine if an existing extension exists.

 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FileNameVal As String If SaveAsUI Then FileNameVal = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm") Cancel = True If FileNameVal = CStr(False) Then 'User pressed cancel Exit Sub End If Application.EnableEvents = False If Right(ThisWorkbook.Name, 5) <> ".xlsm" Then ThisWorkbook.SaveAs Filename:=FileNameVal & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled Else ThisWorkbook.SaveAs Filename:=FileNameVal, FileFormat:=xlOpenXMLWorkbookMacroEnabled End If Application.EnableEvents = True End If End Sub