Here is one approach. You can use a class module to create Application-Level
events. There is an event called "WorkbookBeforeClose" which traps when the
user attempts to close ANY of the open workbooks. If the user clicks the Red
X, this event should be triggered. FYI, there are many other
Application-Level events that can be programmed also.

The events will not be active until they are initialized by the
"Initialize_XL_App_Events" subroutine. I've placed that routine in the
"Workbook_Open" event which is triggered when the workbook is first opened,
provided the user enables macros.

Troy


1) Insert a Class Module in the Excel VBE
2) Rename the Class Module: cls_xlAppEvents
3) Place the following code in the class module codepane.
'===== Class Module Codepane =============================
Option Explicit

Private WithEvents xlApp As Application

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

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
'Place your code of how you want to handle BeforeClose
'for any excel workbook in this session here.
MsgBox "You are not allowed to close the workbook"

'Cancel=True, cancels the user's attempt to close the workbook.
Cancel = True
End Sub
'================================================= ===


4) Insert a standard module
5) Place the following code in the codepane
'===== Standard Module Codepane ===========================
Option Explicit

Dim xlAppEvents As cls_xlAppEvents

Sub Initialize_XL_App_Events()
'Must run this routine when the Excel Application
'first starts to sink the events.
Set xlAppEvents = New cls_xlAppEvents
End Sub
'================================================= ===


6) Open the ThisWorkbook codepane
7) Place the following code in the codepane
'===== ThisWorkbook Codepane ============================
Option Explicit

Private Sub Workbook_Open()
'Initialize the application level events.
Initialize_XL_App_Events
End Sub
'================================================= ===

"danzel " wrote in message
...
I have an application using several open workbooks. I need to detect
when a user tries to close Excel without going through my menu. i.e.
clicking on the 'X'.
Our IT department will not allow adding DLL's to the system so I need a
subclassing skeleton source or any other source-based answer.

Thank you


---
Message posted from http://www.ExcelForum.com/