CODEWITHSUNDEEP
vbaexcelsave
Yellow
Yellow

Reputation: 148

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

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. Grey screen that remains after closing the workbook

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?

Upvotes: 1

Views: 671

Answers (1)

Comintern
Comintern

Reputation: 22185

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.

Upvotes: 2

Related Questions