8/25/16 Update: I updated Office 2016 (now on v 16.0.7167.2040) and it seems to have been fixed. I didn't change any add-ins or anything. Maybe Microsoft does look at these issues! Hopefully, anyone else with this issue can update their version and see this bug has been corrected. (I removed the dropbox link from the OP.)
4/9/16 Update: I updated Office (now on v 16.0.6729.1012) Instead of the .beforeclose event only firing once, now the event fires every other time. Here is a video showing the odd behavior: screencast.com/t/BdkTd9ib
Original Post: Ran across this bug today where the .beforeclose event only fires one time in Excel 2016. The following code works as expected in Excel 2007, 2010, and 2013 (all 32-bit on Windows 10) where the .beforeclose event fires every time. It does NOT work as expected in Excel 2016.
Code in the Workbook.BeforeClose event only fires one time. If the user clicks "Cancel" to a custom save dialog box and Cancel=True so the workbook won't close, and the user goes to close the workbook again, the .BeforeClose does not fire. The default Excel close dialog shows instead.
Steps to reproduce manually: Create new .xlsm workbook. Create a "isDirty" named range. In the sheet1 module put the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1) = [isDirty] Then Exit Sub
Application.EnableEvents = False
[isDirty] = True
Application.EnableEvents = True
End Sub
Input this in the ThisWorkook Module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.EnableEvents = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
With Me
If [isDirty] = True Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation, "CUSTOM CLOSE BOX")
Case Is = vbYes
Call CustomSave
Case Is = vbNo
Me.Saved = True
Case Is = vbCancel
Cancel = True
GoTo Cleanup
End Select
End If
End With
With Application
.EnableEvents = True
.StatusBar = False
.ScreenUpdating = True
.DisplayAlerts = True
End With
Me.Saved = True
Exit Sub
Cleanup: 'user hit cancel to custom save dialog box
With Application
.DisplayAlerts = True
.CalculateBeforeSave = False
.Calculation = xlCalculationManual
.EnableEvents = True
.ScreenUpdating = True
.StatusBar = False
End With
Me.Saved = False
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
[isDirty] = False
End Sub
Insert a custom Module and put this code:
Sub CustomSave()
[isDirty] = False
ThisWorkbook.Save
ThisWorkbook.Saved = True
End Sub
If the user makes a change on Sheet1, the isDirty flag is set to True. User clicks the X to close the workbook and .beforeclose event fires and the custom confirm close dialog box opens. User hits cancel.
User hits X again and now the normal Excel workbook close dialog opens asking if the user wants to save changes. The .beforeclose event does not fire again.
Any suggestions?