Help with Workbook_BeforeClose

[RiffRiffington]

Hello, I would assume this would be a simple enough question that I wouldn't have to start a new thread. However, I have searched far and wide for an answer and have come up with nothing. So I am trying to figure out the proper function of the "Workbook_BeforeClose" event. My understanding is that it should run the code when you close Excel or the workbook. Consider the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    MsgBox ("Test")


End Sub

My understanding is that this code should display a message box that says "Test" when I click on the "X" to close Excel (or the workbook). However, when I try this, nothing happens. I have tried using this code in the following modules: "This Workbook", "Sheet1", and "Module1".

I also know that Excel is not even attempting to run the code because if I throw some bogus code in there, it does not throw an error. Also, Excel will not even allow me to do a "Step Into" with the debug toolbar. As long as the sub is titled with the "Workbook_BeforeClose" event, its as if Excel is ignoring it all together. I am using Excel 2010. Any help would be greatly appreciated! Thanks!

 

[RoryA]

That code has to be in the ThisWorkbook module of the workbook you are closing.

 

[RiffRiffington]

UPDATE: I found a workaround for this which will allow me to run code when any workbook closes. In "Personal.xlsb", I create a Class1 module and use the following code:

Private Sub appevent_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)


    MsgBox ("Test1")


End Sub

Then in a new module "Module 2", I use the following code:

Sub Test()
    
    Set myobject.appevent = Application
    
End Sub

This works for now. If anyone has any suggestions on how to do it better, I'm all ears. Otherwise, consider this thread self-resolved. Thank you.