Excel Template Files

Originally Posted by JimDandy

I've tried using VBA to set the file type in various ways thanks to the helpful suggestions from this group, and as long as the user doesn't cancel out of the dialog or mess things up, it works fine but selecting Cancel, for instance, during the save-as causes the worksheet to save itself anyway with "FALSE.xlsm" as the file name....it does get the correct file type, but I cannot have all my files trying to save to that name.

Can you show your code?

Just move the one line to where it is below. If they cancel, the file is not saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim strSaveName As String
    Dim var    As Variant
    Dim strNewPart As String
    With ThisWorkbook
        If .Path = vbNullString Then
            Cancel = True
            strSaveName = "Underwriting (template).xlsm"
            var = Application.GetSaveAsFilename(strSaveName, fileFilter:="Excel Files (*.xlsm), *.xlsm")
            If var <> False Then
                Do While InStr(1, var, "template") > 0
                    strNewPart = InputBox(prompt:="Enter the partial new name for saving")
                    If Len(strNewPart) > 0 Then var = Replace(var, "template", strNewPart)
                Loop
            Else
                Exit Sub
            End If
            Application.EnableEvents = False
            .SaveAs var, FileFormat:=52
            Application.EnableEvents = True
        End If
    End With
End Sub

Moved to new thread

It doesn't have anything to do with the message box. You would test if the File Name returned from .GetSaveAsFilename prompt equals False to determine if they canceled that prompt.

This site has some good code examples
Use VBA SaveAs

Private Sub CommandButton1_Click()
    
    Dim fname As Variant
    Dim FileFormatValue As Long
    
    If MsgBox("Do you wish to save as a new file?", vbYesNo, "Save As Option") = vbYes Then
    
        'Give the user the choice to save in 2000-2003 format or in one of the
        'new formats. Use the "Save as type" dropdown to make a choice,Default =
        'Excel Macro Enabled Workbook. You can add or remove formats to/from the list
        
        fname = Application.GetSaveAsFilename(InitialFileName:="", filefilter:= _
            " Excel Macro Free Workbook (*.xlsx), *.xlsx," & _
            " Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _
            " Excel 2000-2003 Workbook (*.xls), *.xls," & _
            " Excel Binary Workbook (*.xlsb), *.xlsb", _
            FilterIndex:=2, Title:="Save File As...")
        
        If fname <> False Then

            'Find the correct FileFormat that match the choice in the "Save as type" list
            Select Case LCase(Right(fname, Len(fname) - InStrRev(fname, ".", , 1)))
                Case "xls": FileFormatValue = 56
                Case "xlsx": FileFormatValue = 51
                Case "xlsm": FileFormatValue = 52
                Case "xlsb": FileFormatValue = 50
                Case Else: FileFormatValue = 0
            End Select
            
            'Save the file with the xlFileFormat parameter
            'value that match the file extension
            If FileFormatValue = 0 Then
                MsgBox "Sorry, unknown file extension"
            Else
                'Save the file in the format you choose in the "Save as type" dropdown
                ActiveWorkbook.SaveAs fname, FileFormat:=FileFormatValue, CreateBackup:=False
                'ActiveWorkbook.Close False
            End If
            
        End If
        
    End If
End Sub