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

 

[Realtreegirl75]

It looks to me like this doesn't work. If I hit cancel, it still goes through the rest of the macro to clear out the document and then gives you the option to save for the second time.

Is there any chance that there is a way to disable the cancel button when the save box opens?

 

[Realtreegirl75]

I don't think it works....when I run the macro, if I click cancel, it still goes through the process of clearing the document and then giving the user the option to save again.

As an alternative, is there a way to disable the cancel button in the save dialogue box?

 

[Alex Blakenburg]

Then I misunderstood what you wanted.
My first If saves if <> False.
Just change the first if statement to:

If FileSaveName = False Then Exit Sub

 

[Alex Blakenburg]

A more detailed version with the above modification:

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 Exit Sub
ThisWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52


'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
        ' the else is not required if there is not code after this point
        Exit Sub
    End If

 

[Realtreegirl75]

Sorry about the delay for this. I was moving across the country lol. Ok so I finally got to try this and it is working like a champ....except for it won't let me save over an existing file. When the user clicks the Complete Today's Report button, it saves with todays date (lets say 3/5/23) and it saves again with tomorrow's date (3/6/23). This means that tomorrow", the user just opens the file with the correct date (3/6/23) to fill out their report.

So I go through the motions for 3/5/23, it saves beautifully, it exits the sub if I click Cancel instead. Its perfect!

However, when I open the report dated 3/6/23 and click Complete Today's Report and try to save it in the same manner, I get: Run-time error '1004': Cannot access 'Dir - MPID - tday - inspect.xlsm'.
It specifically gets hung up on this line:

ThisWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=52

Any additional ideas?

 

[Alex Blakenburg]

When it errors out.
• click on debug
• in the immediate window (ctrl+G it is not visible)
paste the following line and hit enter (include the question mark)

? FileSaveName,

1) visually check that what you see makes sense
2) copy what you see and put it into a windows explorer window and hit enter to see if the path is correct. If not work out what is wrong with the path.
You could also try Excel File > Open and paste it in there.
Note: SaveAs using the same name as the open workbook will give you a 1004 error message but the message text would be different to what you are showing in your post.