Trapping Excel Close Event

  • Thread starter Thread starter Bob J.
  • Start date Start date
B

Bob J.

Can anyone help with trapping the application.quit event
that is triggered by clicking on the close (X) box in the
upper right title bar or when the user performs a
File_Exit command and there is no Save Box genereated?
Workbook_close and _Deactivate do not seem to work.

Thanks

Bob
 
Bob,

There is no Application Close or BeforeClose event. However, you can trap the Before_Close event for a workbook that results from
the closure of the application. You can do this for all open workbooks by trapping application events. Try placing the following
code in the ThisWorkbook module of a workbook such as Personal.xls:

Option Explicit

Dim WithEvents xlApp As Excel.Application

Private Sub Workbook_Open()
Set xlApp = Excel.Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
'Do stuff
End Sub

"Do stuff" will be executed when any workbook is closed and can include setting Cancel = True to prevent the closure of the workbook
and the application.
 
-----Original Message-----
Can anyone help with trapping the application.quit event
that is triggered by clicking on the close (X) box in the
upper right title bar or when the user performs a
File_Exit command and there is no Save Box genereated?
Workbook_close and _Deactivate do not seem to work.

Thanks

Bob
Thanks, your code worked!
Bob

.
Click to expand...
 
As replied to the previous posting of this question:

There is no event fired for Excel itself closing. You can trap workbooks
closing with the BeforeClose Event. It sounds like you want this done at
the application level, so you would need to instantiate application level
events. See Chip Pearson's page on this

http://www.cpearson.com/excel/appevent.htm
 
Back
Top