I need to trigger an event macro, but can't work out how

Posted by Stuart on January 15, 2002 7:24 PM

In the event of the user closing an active workbook, I want a macro to run, but can't work out how. I have thought about if the user clicks the close button, but it needs to be if the user clicks close, clicks the x at top right corner, or if the user selects file>close from the menu.

Any help appreciated.
Stuart

Posted by Ivan F Moala on January 15, 2002 7:31 PM

Use the

Private Sub Workbook_BeforeClose(Cancel As Boolean)

This is in the Thisworkbook object module
avial via.......

Alt + F11
The Ctrl + R to view your project

Then double click on the Thisworkbook

Left dropdown pane select Workbook
Right dropdown pane select Before close.

HTH

Ivan

Posted by Stuart on January 15, 2002 7:31 PM

I thought I should add to the below, that this is for use in an add-in, and therefore workbook close macro does not suffice. Thnx

Posted by Stuart on January 16, 2002 10:50 PM

Ivan, that wasn't the answer I was after, can you (or anyone) look at my second post. Thnx

Posted by Ivan F Moala on January 17, 2002 12:53 AM

Create a Class module and paste this code;

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox "running macro now"
'Your code here

End Sub

Ivan