cancel or esc button in the dialog box saves a file named "False"

Question

I wrote a code to save a new file only with sheet1. The problem is that is I clic on esc or cancel in the dialog box when saving the file, the new files saves in the directory as FALSE, instead I would like to not save the file if I click esc or cancel

I tried to put an if such as : If thisfile="FALSE" then exit sub; but it does not work.

Sub test()
Dim thisfile As Variant

    thisfile = Range("Y5").Value & Range("C16").Value & "_" & Range("K41").Value

    Worksheets("Sale Dispo Approval").Copy

    With ActiveWorkbook

        .SaveAs Application.GetSaveAsFilename(InitialFileName:=thisfile, fileFilter:="Excel Files (*.xlsx), *.xlsx")

        .Close savechanges:=False
    End With

End Sub

I expect that one i click cancel or esc no new files are saved.

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-06-27T12:14:12

The thing is that you tust a user input that you get from GetSaveAsFilename and push it directly into SaveAs without validating. That is a bad practice, because user input should (in gerenral) be validated before using it.

You assumed that the Application.GetSaveAsFilename method always returns a String with a file name but according to the documentation it returns a Variant that can either be a String (filename) or a Boolean in case of cancel.

Change your .SaveAs line to

Dim FileName As Variant 'cancel returns Boolean
FileName = Application.GetSaveAsFilename(InitialFileName:=thisfile, fileFilter:="Excel Files (*.xlsx), *.xlsx")

If FileName = False Then
    Exit Sub 'exit if user pressed cancel
End If

.SaveAs FileName

cancel or esc button in the dialog box saves a file named "False"

1 Answers