Peculiar excel event behaviour

[Jaafar Tribak]

Hi all.

I have this simple code in ThisWorkbook module that is supposed to hook the Apllication events so that the AppEvents_WorkbookBeforeClose event hadler is fired each time an open workbook is closed.

Option Explicit
 
Private WithEvents AppEvents As Application
 
Private Sub Workbook_Open()
 
    Set AppEvents = Application
    
    Call MyLoop
 
End Sub

Private Sub AppEvents_WorkbookBeforeClose _
(ByVal Wb As Workbook, Cancel As Boolean)
 
    MsgBox "Workbook " & Wb.Name & " closing..."
 
End Sub
 
Private Sub MyLoop()
 
    Do
        DoEvents
    Loop
 
End Sub

Now the problem I am having is that if I close a workbook, the BeforeClose event code fires as expected but if I close the main Excel application window, the routine never gets fired.

I know this is due to the background loop routine because when I remove the loop routine everything works as expected.

Can anybody shade some light as to why this is happening and perhaps provide a workaround .

Thanks .

 

[mikerickson]

I believe that AppEvents has to be Public if it's to read the event.

 

[Jaafar Tribak]

Thanks Mike but that made no difference.

 

[RoryA]

This is a bit of a guess, but I would suspect that the abrupt termination of the loop codde causes a reset and your application variable to lose state.

 

[Jaafar Tribak]

This is a bit of a guess, but I would suspect that the abrupt termination of the loop codde causes a reset and your application variable to lose state.

Thanks Rory. That is what I thought .

I really need that loop so I am still trying to solve this problem. If I come to a solution i'll post it here.