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.

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.

--

Dave Peterson

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.

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.

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!

' 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.

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.

"Robert Schwenn" wrote in message
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.

Good, I misunderstood exactly what you were after.

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?


If the user can be persuaded to manually add the Com-Addin registration
occurs automatically. Job done. The normal way to distribute a Com-Addin is
with an Installer. Thinking aloud, as in I haven't done it but I don't see
why not, your VBA could do the following:

1. Check if the ComAddin is installed
attempt to reference the ComAddin, if not -

2. register the dll Shell Regsrv32
That's doable although there's a fair bit involved overall

3. Add registry entries
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\ Addins\MyAddin.Connect
and four sub keys

4. Application.COMAddIns.Update

Steps 2 & 3 are what an installer would do, having enuserd there are no
running instances of Excel.


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...

I agree and would bin any file sent to me that did that. If following that
route it would be sensible to add a message (in code previously posted)
advising user what was about to occur (close all wb's & quit with options to
save changes) with the option to abort.

However I wouldn't take much more kindly to not being able to close any file
as I wish, which if I follow is what you want to do, ie keep your open
permanently until Excel quit.

Regards,
Peter T