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!

 

[JoeMo]

Code works fine for me on both the first and second attempts to close.

 

[MUKESHY12390]

dude.. I have just checked and it's working fine.
might be you need to take one more try.

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

 

[ramulose]

Thanks guys. I have tried many, many times (Maybe 20 or 30?). It's definitely not working here. Do either of you use 2016? I'm wondering if it could have anything to do with that.

 

[ramulose]

By the way. How do you use code tags so the code looks like code? I can't find them in the editor.

 

[MUKESHY12390]

first press alt+f11

click to thisworkbook and paste entire code then save.
then try to close and select NO on msgbox

 

[ramulose]

I already have the code in the IDE. It works the first time, just not the second time.

 

[ramulose]

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.

 

[MUKESHY12390]

ok .. can you tell me what is the file extension and in name of the module where you have the code .

 

[ramulose]

So it doesn't look like I can edit my OP. If you discover that this is a problem with 2016, one of the administrators might want to add the '2016' tag to it.