Save as MarcoEnabled Workbook - Still saving on 'cancel'

[eli_m]

Hi,

I have this code

Sub SaveQuoteAsWorkbook()

Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = Range("H4") & "" & Format(today, "DD-MM-YYYY")
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52

End Sub

1. Run the Macro
2. Dialogue pops up to save:

3. I select 'Cancel'
4. and still it saves the Macro Enabled Template as 'False.xlsm'

How can I stop it from actually saving? I thought 'Cancel' would stop it.

Thanks in advance

 

[MARK858]

Try...

FileSaveName = Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
If FileSaveName = False Then Exit Sub

Declare FileSaveName as Variant

 

[Alex Blakenburg]

@MARK858 the OP will need to change the Dim FileSaveName to Variant (currently String) or is will give a type mismatch.

The below seemed to work as well if you wanted to keep FileSaveName as String

Sub SaveQuoteAsWorkbook()

Range("H4").Copy
Range("H4").PasteSpecial xlPasteValuesAndNumberFormats

Dim tDate As String
Dim FileSaveName As String
Dim fName As String

fName = Range("H4") & "" & Format(Date, "DD-MM-YYYY")
FileSaveName = CStr(Application.GetSaveAsFilename(InitialFileName:=fName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file"))
If FileSaveName <> "False" Then ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52

End Sub

 

[MARK858]

@MARK858 the OP will need to change the Dim FileSaveName to Variant (currently String

Already spotted and edited

 

[eli_m]

Thanks guys!

That worked

 

[MARK858]

You're welcome, happy we could help

 

[Realtreegirl75]

Ok guys, I have an additional part to the puzzle for you:

If I run my macro to save and I hit cancel it exits the sub
If I run my macro to save and I hit Save.....it still exits the sub.

Is there a way to have it continue through the sub if you click save but to exit if you click cancel?

 

[Alex Blakenburg]

You need to show us the actual code you are using, preferably by inserting it here using either the VBA or RICH button.

 

[Realtreegirl75]

Sorry about that - here is the code. I need it to continue to clear the cells and then save it a second time if the user saves the first document but to exit the code and not clear/save if they cancel the first save.

MPID = Range("r3").Text
tday = Range("ac4").Text
inspect = Range("E6").Text
ENO = Range("AC3").Text
tmr = Range("T237").Text
tdayName = "DIR - " & MPID & " - " & tday & " - " & inspect
tmrName = "DIR - " & MPID & " - " & tmr & " - " & inspect

MsgBox "Click YES when prompted to save over an existing file"
Application.Dialogs(xlDialogSaveAs).Show tdayName
ThisWorkbook.Save
If FileSaveName = False Then Exit Sub

'FIRST SECTION
   Range("g7:m7").ClearContents
   Range("e8:g8").ClearContents
   Range("k8:m8").ClearContents
   Range("f10:I10").ClearContents
   Range("r7:w7").ClearContents
   Range("r8:w8").ClearContents
   Range("o10:r10").ClearContents

Application.Dialogs(xlDialogSaveAs).Show tmrName

'LAST CLEAR, SAVE AND CLOSE
    Range("ac4:AJ4").ClearContents

ThisWorkbook.Save

 

[Alex Blakenburg]

I have not been able to test this but see if the below works for you.
Since you are trying to capture cancelling the Save, I wouldn't use the pplication.Dialogs(xlDialogSaveAs)

MPID = Range("r3").Text
tday = Range("ac4").Text
Inspect = Range("E6").Text
ENO = Range("AC3").Text
tmr = Range("T237").Text
tdayName = "DIR - " & MPID & " - " & tday & " - " & Inspect
tmrName = "DIR - " & MPID & " - " & tmr & " - " & Inspect

MsgBox "Click YES when prompted to save over an existing file"

'Application.Dialogs(xlDialogSaveAs).Show tdayName
Dim FileSaveName As Variant
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=tdayName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
If FileSaveName <> False Then
    ThisWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52
End If

'FIRST SECTION
   Range("g7:m7").ClearContents
   Range("e8:g8").ClearContents
   Range("k8:m8").ClearContents
   Range("f10:I10").ClearContents
   Range("r7:w7").ClearContents
   Range("r8:w8").ClearContents
   Range("o10:r10").ClearContents

'Application.Dialogs(xlDialogSaveAs).Show tmrName
'LAST CLEAR, SAVE AND CLOSE
    Range("ac4:AJ4").ClearContents
    
    FileSaveName = Application.GetSaveAsFilename(InitialFileName:=tmrName, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
    If FileSaveName <> False Then
        ThisWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52
    Else
        Exit Sub
    End If