Workbook_BeforeClose not fired the second time on Excel 2016

[fabri9]

On Excel 2016 RTM if I close the Workbook and on the event Workbook_BeforeClose I cancel the closure with
Cancel=True
when there is only one Workbook open, the second time I close the Workbook, the event Workbook_BeforeClose do not fired.
I think this is a bug of Excel 2016.
Note: Application.EnableEvents is True.

Have You an idea of how to get around this bug?

 

[portews]

Try setting Cancel to False before you exit the sub

 

[fabri9]

I need to set Cancel=True because I need to cancel the Workbook closure.

 

[portews]

Not what I meant. I think the reason it's not working the second time is that the program is remembering that you cancelled once. After you have hit the cancel button, and you are already committed to quitting, reset the variable so the next time you go into the sub it the code won't think you hit the Cancel button again.

I assume your code looks something like this:

If Cancel=True then Exit Sub

Instead try:

If Cancel=True then 
     Cancel=False
     Exit Sub
End if

 

[fabri9]

I try to explain better.
I have the following code on ThisWorkbook (simplified):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = True
End Sub

When I close the Workbook by user interface (X button of the window) the event Workbook_BeforeClose is fired and the closure is canceled because I set Cancel=True.
The second time I close the Workbook by user interface (X button of the window) the event Workbook_BeforeClose is NOT fired and I can not cancel the closure so the Workbook closes.
The code above work on all Excel version; only on Excel 2016 not work.
The Cancel Variable is a parameter ByRef of the event Workbook_BeforeClose and therefore it is local.

 

[MiKue]

Hello fabri9,

i've the same problem as you. Did you get a helpful answer yet? Or did you find out a workaround?

 

[fabri9]

At the moment I have not found any solution to the problem.

 

[fabri9]

I found that installing the "August 8, 2017, update for Excel 2016 (KB3085435)", the problem is resolved.
You can find it at this address: https://support.microsoft.com/en-us/help/3085435/august-8-2017-update-for-excel-2016-kb3085435
Read carefully the section "Registry information" present in the article.

If you do not have just excel, I suggest to install all the "August 2017, updates for Microsoft Office":https://support.microsoft.com/en-us/help/4036121/august-2017-updates-for-microsoft-office

Note: Manual update work only if you have installed the Office 2016 standard MSI; if you have installed the ClickToRun edition you must update with the automatic mode.