Peter T wrote:
I recall the discussion which (I think) led to Chip's Com-Addin but I'm not
sure it will do quite what you want. Hopefully Chip will advise either way.

When the Com-Addin is loaded, it does exactly what I want. The sore point is,
that it's functionality would be needed on machines, where the Com-Addin is not
registered. I only could place it on a mapped network share. So the next question:
== Is it possible to load the Com-Addin (with a known path) from within a VBA
project, although the Com-Addin is not registered on the system?



As a different approach maybe consider any attempt to close your file as
equivalent to closing Excel. If that's feasible have a go with the
following, lightly tested and no doubt with holes, hopefully plug'able!

It's an idea. But i believe, that it's amazing for the user to see any other
workbooks to be closed...
Thanks for Your work.



' in the ThisWorkbook module of your file
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim bAbortQuit As Boolean
Dim bNotSaved As Boolean
Dim s As String, sMsg As String
Dim vbAns As VbMsgBoxResult
Dim wb As Workbook

If Not Me.Saved Then
' this bit to pre-empt the save (this book) dialog
sMsg = "Do you want to save the changes you made to " & Me.Name
vbAns = MsgBox(sMsg, vbExclamation Or vbYesNoCancel)
If vbAns = vbNo Then
bNotSaved = True
Me.Saved = True
ElseIf vbAns = vbYes Then
On Error Resume Next
Me.Save ' error if user aborted never saved wb
On Error GoTo 0
bNotSaved = Not Me.Saved
End If
End If

If Me.Saved = False Then
' user aborted save
Cancel = True
Exit Sub
End If

'close all wb's,
'any unsaved will show the save dialog
'if user aborts the wb's name will still exit
' and NOT error on attempt to read it - if no error abort

On Error Resume Next
For Each wb In Workbooks
If Not wb Is ThisWorkbook Then
wb.Close
s = wb.Name
If Err = 0 Then
' user pressed cancel in save dialog
bAbortQuit = True
Exit For
End If
Err.Clear
End If
Next

If Not bAbortQuit Then
Application.Quit
Else
If bNotSaved Then Me.Saved = False
Cancel = True
End If

End Sub

Regards,
Peter T



"Robert Schwenn" wrote in message
...
Thanks, very helpful.
Robert



Dave Peterson schrieb:
Take a look at Chip Pearson's site:
http://cpearson.com/excel/ExcelShutdown.htm



Robert Schwenn wrote:
Hi,
I can't find such an event (Office 2003). Is it possible with VBA to
detect that
Excel is going to be closed?

My goal:
I want to prevent a workbook from being closed by the user as long as
Excel is
running. This is no problem with this eventhandler:

Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub

But in this way the workbook never is closed, even when Excel should
close...
Thanks,
Robert.