VBA Workbook_BeforeClose doesn't fire twice in a row

[ramulose]

This is an interesting problem that can be reproduced with only 4 lines of code so I think it might be a bug, unless I am doing something wrong (Which is entirely possible).

To recreate the problem, execute the following steps.

1. Open a new workbook
2. Copy the following code into the ThisWorkbook object

Private Sub Workbook_BeforeClose(Cancel As Boolean)
UserResponse = MsgBox("You wanna to bail?", vbYesNo)
If UserResponse = vbNo Then
Cancel = True
End If
End Sub

3. Save the file
4. Click the X in the upper right corner of the SPREADSHEET to close it
5. It will ask "Wanna Bail?"
6. Answer 'No'
7. (It doesn't close as expected)
7. Click the X in the upper right corner of the SPREADSHEET again.
8. The spreadsheet closes without firing the event

Any insights?

Thanks!

 

[ramulose]

I figured it out Mukeshy. See my answer above. Thanks for the responses.

 

[JoeMo]

WOW!!!! I figured this out. If I add am Application.EnableEvents call after the 'No' Response, it works! Very strange.

Here's the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
UserResponse = MsgBox("You wanna bail?", vbYesNo)
If UserResponse = vbNo Then
Cancel = True
Application.EnableEvents = True
End If
End Sub

It seems like events are being turned off when you cancel. This could be an issue with 2016. If you know anyone that has it, they should try it out.

The fix is simple, but the reason for it is a little disconcerting. This could indicate that other things are broken with this new version as well.

If I can still edit my original post, I will add 2016 to the tags and the title.

Thanks for the responses guys.

Are you sure there is no other event code in your workbook?

 

[ramulose]

Hi Joe - sorry for the delayed response. Yes I'm sure there are no other events. The only routine in the workbook is the 7 lines of code I posted above.

I said that I fixed it with the application.enablevents = true function, but it still happens randomly. I'm not sure what going on, but for right now, this is just an application that I will be using so I can live with it. I would still love to find an answer because I'm sure I'm going to need it for something I'm going to release into the wild at some point. I guess it could be something wrong with my system. Is there somewhere I can post a workbook so you can actually try it on your computer?

Thanks

 

[JoeMo]

Hi Joe - sorry for the delayed response. Yes I'm sure there are no other events. The only routine in the workbook is the 7 lines of code I posted above.

I said that I fixed it with the application.enablevents = true function, but it still happens randomly. I'm not sure what going on, but for right now, this is just an application that I will be using so I can live with it. I would still love to find an answer because I'm sure I'm going to need it for something I'm going to release into the wild at some point. I guess it could be something wrong with my system. Is there somewhere I can post a workbook so you can actually try it on your computer?

Thanks

I'm using Excel 2010 so my results may differ from your Excel 2016 results. However, if you would like to PM me I will send you an email address so you can send me your file.