Workbook BeforeClose not calling after opening VBA editor

[Shundra9]

Hello all,

As the title suggests, I am having a problem with Excel's Workbook_BeforeClose event handler. I know I am using the correct method signature (shown below) because it has worked in the past, and I have correctly placed the event handler in the "ThisWorkbook" module of the VBA editor. However, I have noticed that whenever I do a significant amount of coding within the VBE (in other modules), this method is not executed. Even when I place breakpoints inside, the breakpoints are not hit before the workbook closes. Again, this only happens if I have been messing around with other modules in the VBE; if I just open the workbook and immediately close it, this method runs fine and the breakpoints get hit. Saving or not saving the workbook does not seem to make a difference, and this problem occurs in Excel 2013 and 2016 (haven't tried opening the workbook in earlier versions). This issue is really starting to impede my workflow, so if anyone has a thought on why my Workbook_BeforeClose handler is not being called, I would very much appreciate the help!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'More code...
End Sub

 

[JoeMo]

One possibility is that while you were "messing around with other modules in the VBE" you inadvertently disabled events. To fix this open the immediate window in the VB Editor. Type "Application.EnableEvents" (w/o the quote marks) and press enter. Then try closing the workbook again.

 

[Shundra9]

@JoeMo Wow I didn't even think of that! You're absolutely right, my other modules contain some optimization code, which includes disabling Application events while a macro is running, then turning them back on after completion. However, while debugging, I would obviously occasionally stop the macro before it completed, so Application events weren't getting turned back on!

Is there any way that I can turn events back on without running "Application.EnableEvents = True" programmatically? I don't see an option for it under Workbook properties in the VBE.

 

[JoeMo]

@JoeMo Wow I didn't even think of that! You're absolutely right, my other modules contain some optimization code, which includes disabling Application events while a macro is running, then turning them back on after completion. However, while debugging, I would obviously occasionally stop the macro before it completed, so Application events weren't getting turned back on!

Is there any way that I can turn events back on without running "Application.EnableEvents = True" programmatically? I don't see an option for it under Workbook properties in the VBE.

No other way that I am aware of.

 

[Shundra9]

@JoeMo Actually, I never realized that you could run code in the Immediate Window outside of a debugging session. Typing that line before closing the workbook seems like a fine solution. Thank you so much for your help!

 

[JoeMo]

@JoeMo Actually, I never realized that you could run code in the Immediate Window outside of a debugging session. Typing that line before closing the workbook seems like a fine solution. Thank you so much for your help!

You are welcome - thanks for the reply.