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" <(E-Mail Removed)> wrote in message
news:frs7n5$oo0$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:frs7n5$oo0$(E-Mail Removed)...
>> 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

Peter T wrote:
> "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

I suspect that this step needs admin rights ...
And I understand Your answer so, that a ComAddin can't be used before it is
registered on the system.
Thanks.
Robert.

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

"Robert Schwenn" wrote in message
> Peter T wrote:
> > "Robert Schwenn" wrote in message
> >> Peter T wrote:

<snip>

> > 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
>
> I suspect that this step needs admin rights ...

Generally that would require proactive blocking by the administrator,
typically it works (see re Vista below).

Why not test for yourself. Before running the following, uninstall the
ComAddin from Excel (Tools > ComAddins), Quit & Restart Excel. Toggle
bUnREgister to un/install. Hope what I've hardcoded to sCls & sFile are
correct, taken from an early example of Chip's addin.

Sub TestRegServ32()
Dim bIsReg As Boolean
Dim sFile As String
Dim sFullDllName As String
Dim sPath As String
Dim sCls As String
' Manually uninstall the ComaDDin from Tools > ComAddins before
' running this test or you'll get inconclusive reustls

sPath = "c:\Path-to-the-dll\"

' the public connect class in Chip Pearson's CommAddin
sCls = "TestExcelShutdown.ExcelConnect"
sFile = "TestExcelShutdown.dll"

sFile = Chr(34) & sPath & sFile & Chr(34)

bUnREgister = False
If bUnREgister Then
sFile = sFile & " /u" ' add the unregister flag
End If

vRet = Shell("Regsvr32 /s " & sFile)

bIsReg = DllIsReg(sCls)

MsgBox bIsReg
End Sub

Function DllIsReg(sClsName As String) As Boolean
' sClsName a public class in the dll

Dim oComDll As Object

On Error Resume Next
Set oComDll = CreateObject(sClsName)

DllIsReg = Not oComDll Is Nothing

End Function

Above is a stripped down version of something I have to (un)install a dll as
required. I wouldn't test the dll is registered each time (say in wb open)
by running DllIsReg, instead wait for some call to the dll to fail then do
it.

> And I understand Your answer so, that a ComAddin can't be used before it
is
> registered on the system.

That's certainly my understanding but normally Regsvr32 works - except I
can't get it work in a non-administrator's account in Vista!

A bit more - contrary to what I speculated previously (see below), even
having registered the dll, added the correct registry entries, doing the
ComAddins.Update will add the addin to the collection but it will not cause
the addin to load. I assume there is a way to programatically load it but
not sure how. In effect it would mean waiting for the next time Excel starts
for the addin to auto load.

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


Regards,
Peter T

"Peter T" wrote in message

just this bit -

> A bit more - contrary to what I speculated previously (see below), even
> having registered the dll, added the correct registry entries, doing the
> ComAddins.Update will add the addin to the collection but it will not
cause
> the addin to load. I assume there is a way to programatically load it but
> not sure how. In effect it would mean waiting for the next time Excel
starts
> for the addin to auto load.

I wasn't thinking - after adding the registry entries and doing the
Regsrv32:

Sub LoadCAITest()
Dim cai As COMAddIn
Application.COMAddIns.Update
For Each cai In Application.COMAddIns
If cai.Description = "Test Excel Shutdown" Then
cai.Connect = True
Exit For
End If
Next
End Sub

Regards,
Peter T