4

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?

Luuklag
  • 3,897
  • 11
  • 38
  • 57
michaelf
  • 469
  • 6
  • 20
  • I don't understand the motivation. Seems like a redundant "do you want to save before close" in addition to excel's? – findwindow Feb 24 '16 at 16:57
  • I don't want to use the built in Excel save before dialog at all. I have a custom built one that runs other code depending on the users' response. The problem is the workbook.beforeclose only fires one time in Excel 2016 version, but fires every time in other Excel versions. There is a lot more to my actual workbook, I just stripped it down to a bare minimum to illustrate this bug. – michaelf Feb 24 '16 at 17:07
  • If you watch your dirty flag when does it get set to false if you hit cancel? – Raystafarian Feb 24 '16 at 18:45
  • It does not change when I hit cancel. It only changes when the workbook is saved. The issue isn't with the isDirty flag changing, as it shouldn't impact the workbook.beforeclose event. It's only firing one time in this version of Excel, while it works fine in previous versions. – michaelf Feb 24 '16 at 20:13
  • Unfortunately, not getting any help on this Excel 2016 bug here at stack overflow. I looked but can't find a way to submit a bug to Microsoft. Does anyone know the process of submitting a bug to Microsoft? – michaelf Mar 30 '16 at 01:01
  • So I updated my version of office and now the .beforeclose function alternates between my custom close box and the default Excel close dialog. You can see a short video of it here: http://www.screencast.com/t/Mh3zI1HaXpX9 – michaelf Apr 08 '16 at 20:13
  • Better screencast showing VBA window: http://www.screencast.com/t/BdkTd9ib – michaelf Apr 08 '16 at 20:31
  • Having the same problem. did you find any solution or explanation? – yngrdyn Apr 11 '16 at 20:34
  • Nope. Nothing. I put a post on mrexcel as well. I don't know how/where to report a bug, which this is. – michaelf Apr 13 '16 at 00:34
  • I'm facing the exact same situation. Thanks @michaelf for the very fine explanation and video. Haven't found any fix yet : [ – Leo Gurdian Aug 18 '16 at 23:20
  • It works ok for me (Office 2016). I even did a clean install of it to make sure. I even tried to install BlueBeam Revu to have the same add-in as you. My lucky guess is that it has to do with your Add-ins. Try to disable all your "Active Application Add-ins" along with your COM Add-ins. – Michael Aug 25 '16 at 11:20
  • I updated my version and it works as expected now. Thanks Microsoft! – michaelf Aug 25 '16 at 20:20

1 Answers1

0

Update to the latest version of Office and this shouldn't be an issue any longer. Took a few months but it's fixed.

michaelf
  • 469
  • 6
  • 20