Hi All,
How do i exactly trap the close event of a excel workbook. I need to now the
name of the workbook which is closed. I tried using Workbook_BeforeClose
event.But if the user presses cancel this event is of no use. Then i went
though the Pearson events http://www.cpearson.com/excel/events.htm tutorial
and combined Window_Deactivate and Beforeclose to get exactly the close
event.When there multiple worksheets open this method also fails. Could any
one tell exact way to catch this event.Any code available would be grateful

Thanks in advance

Thanks
Xcelion

Try something like this in ThisWorkbook

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox Wb.Name
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Xcelion" wrote in message
...
Hi All,
How do i exactly trap the close event of a excel workbook. I need to now
the
name of the workbook which is closed. I tried using Workbook_BeforeClose
event.But if the user presses cancel this event is of no use. Then i went
though the Pearson events http://www.cpearson.com/excel/events.htm
tutorial
and combined Window_Deactivate and Beforeclose to get exactly the close
event.When there multiple worksheets open this method also fails. Could
any
one tell exact way to catch this event.Any code available would be
grateful

Thanks in advance

Thanks
Xcelion

Hi Bob,
Thanks for you reply
This method also ,fires before the user is prompted to save changes. So i
will get
the close event even if the user presses the cancel button.How to avoid
this.If the user presses the cancel i shouldn't get the close event

Awaiting for your reply
Thanks
Xcelion


"Bob Phillips" wrote:

Try something like this in ThisWorkbook

Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
MsgBox Wb.Name
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Xcelion" wrote in message
...
Hi All,
How do i exactly trap the close event of a excel workbook. I need to now
the
name of the workbook which is closed. I tried using Workbook_BeforeClose
event.But if the user presses cancel this event is of no use. Then i went
though the Pearson events http://www.cpearson.com/excel/events.htm
tutorial
and combined Window_Deactivate and Beforeclose to get exactly the close
event.When there multiple worksheets open this method also fails. Could
any
one tell exact way to catch this event.Any code available would be
grateful

Thanks in advance

Thanks
Xcelion