Excel refuses to close when "Save when Close" Prompt is disabled

Question

I have an Excel file, PlannerTool which keeps prompting to save the file when closing, even when it has been saved 2 seconds ago. This is probably due to some volatile code. So I thought I would disable the prompt when the file had been saved within a certain time window. However when I do so, Excel refuses to completely shutdown. It will close the workbook, but a grey screen will remain. See screenshot below.

The code I use to deny the prompt is taken from

VBA workbooks.Close without being prompted to if the user wants to save?

Complete code used provided below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.EnableEvents = False

Dim SaveTime As Date
Dim CurrentTime As Date
Dim TimeDifference As Long
SaveTime = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
CurrentTime = Now

TimeDifference = DateDiff("s", SaveTime, CurrentTime)
'Gets the time difference between closing and saving in seconds

If TimeDifference <= 10 Then             'Saved less than 10 seconds ago so deny prompt
    Application.DisplayAlerts = False    'Code gotten from StackOverflow Question
    ThisWorkbook.SaveAs Filename:="PlannerTool", FileFormat:=xlOpenXMLWorkbookMacroEnabled, ConflictResolution:=xlLocalSessionChanges
    ThisWorkbook.Saved = True            'Tweaked to fit saving format needs
    ThisWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
End If

Application.EnableEvents = True    

End Sub

If the VB Editor is open when closing the workbook, the editor will remain open and display the RibbonX_Code Module. The VBA project containing the workbook PlannerTool and code are no longer displayed in the Project Tree, indicating that they are indeed closed. No difference when I have 1 or multiple workbooks opened, these stay displayed in the tree when I close PlannerTool. Can anyone tell me why Excel won't close properly?

What is ThisWorkbook.SaveAs good for. Why would you need a SaveAs? — Storax
@Storax Just took it from the original code. Saves the file again, just to be sure that any minute differences that could have occured within the time window are saved. Tested when the line is removed, the error still happens. — Yellow
I'd try commenting out the Application.DisplayAlerts = False and see if you're suppressing a dialog that needs to be responded to. — Comintern
Also add Application.enablevents like DisplayAlerts in the code accordingly — Storax
Completely unrelated observation, but wouldn't you also want to update the stored BuiltinDocumentProperties inside the If statement? — Comintern

Comintern Comintern · Accepted Answer · 2018-08-03T12:37:29

I can replicate this behavior locally, and it appears that calling ThisWorkbook.Close is the issue. If you open a workbook and then use the Excel menu to close it, (File -> Close), it does the same thing as your code. In the "manual" case, Excel holds the application open because you opted not to close Excel - just the open workbook.

This should work as intended if you manually check to see if Excel should be closed.

If TimeDifference <= 10 Then             'Saved less than 10 seconds ago so deny prompt
    Application.DisplayAlerts = False    'Code gotten from StackOverflow Question
    ThisWorkbook.SaveAs Filename:="PlannerTool", FileFormat:=xlOpenXMLWorkbookMacroEnabled, ConflictResolution:=xlLocalSessionChanges
    ThisWorkbook.Saved = True            'Tweaked to fit saving format needs
    ThisWorkbook.Close SaveChanges:=False

    Application.DisplayAlerts = True
    If Application.Workbooks.Count = 0 Then
        Application.Quit
    End If
End If

I'd suggest this rather than forcing the application itself to close, as the user may have other documents open in Excel.

Excel refuses to close when "Save when Close" Prompt is disabled

1 Answers