Application Close Event

[Robert Schwenn]

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]

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

 

[Robert Schwenn]

Thanks, very helpful.
Robert

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

 

[Peter T]

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]

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.

 

[Peter T]

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.

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]

in message

Good, I misunderstood exactly what you were after.


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.

 

[Peter T]

"Robert Schwenn" wrote in message

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.


Regards,
Peter T

 

[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