global beforeClose event

[bolo]

Hi all experts!!!!

I have a small problem. I am trying to create a xla addin, that will run a macro before ANY workbook is closed on the machine. How do i go about creating somethig that will catch all spreadsheet closures? I have tried using the "Workbook_BeforeClose" Sub but this doesn't catch closure of other workbooks.

Many thanks for your advice.
i am using XL 2003 here...

 

[shg]

Try this in the ThisWorkbook module of the add-in:

Option Explicit
 
Dim WithEvents app As Application
 
Private Sub Workbook_Open()
    Set app = Application
End Sub
 
Private Sub app_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    ' your code here
End Sub

 

[bolo]

Thanks Shg that did the trick!

 

[shg]

You're welcome, glad it helped.