I have a macro enabled excel template, when the user opens a new document aspects of their data they entry create a filename in a designated cell. This code forces the excel document to be saved as a macro-enabled extension and no matter how they name it it will be named what is stated in the designated cell for the filename. 99% of the time it works great; however 1% of the time the file is saved without any extension and is unusable, it is not an excel document. Can someone review this code and offer any suggestions?
Code
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xFileName As String
If SaveAsUI <> False Then
Cancel = True
ThisFile = Range("W2").Value
xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")
If xFileName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
Else
MsgBox "Action Cancelled"
Cancel = True
Exit Sub
End If
End If
End Sub
Display More