Two ways
From Patrick Molloy
******************
You can trap an application level event for this
Here's an example XLA
1) start a new workbook.
2) add a class module,name it clXL
3) in the class module code page addthis
Option Explicit
Private WithEvents xl As Excel.Application
Private Sub xl_WorkbookBeforeClose(ByVal Wb As Workbook, _
Cancel
As Boolean)
Cancel = _
(MsgBox("OK", vbYesNo, "Closing " & Wb.Name) = vbNo)
End Sub
Private Sub Class_Initialize()
Set xl = Excel.Application
End Sub
4) add a standard code module
5) in the code sheet of the standard code module add this code:
Option Explicit
Public xl As clXL
Sub Auto_Open()
Set xl = New clXL
End Sub
6) save the workbook as an XLA, remember where as we'll open it again soon!
close excel
Open excel.
with the add-in manager open the XLA...browse to it if need be
When the add-in opens, the auto_open sub creates the variable set to the
open instance of excel.
Closing a workbook or excel itself will raise a message.
This is a simple example to demonstrate how simple it is to use application
level events.
*********************************
Or try this example posted by Keepitcool
however following will just Dis/Enable the system menu on the window
Note that if you plan to use it on xl97:
you'll need to change application.hwnd with a FindWindow call
Private Declare Function GetWindowLong Lib "user32.dll" Alias _
"GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32.dll" Alias _
"SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Sub DisableX()
With Application
SetWindowLong .hwnd, -16, GetWindowLong(.hwnd, -16) And Not &H80000
End With
End Sub
Sub EnableX()
With Application
SetWindowLong .hwnd, -16, GetWindowLong(.hwnd, -16) Or &H80000
End With
End Sub
