Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Close Event
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Close Event
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Close Event
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Close Event
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Close Event
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Close Event
"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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Close Event
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Close Event
"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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application Close Event
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
captcure close application event | Excel Worksheet Functions | |||
Before Close Event Hide all Worksheets then Close Application | Excel Programming | |||
difference application.quit & application.close | Excel Programming | |||
How to Close an application? | Excel Programming | |||
macro to close excel application other than application.quit | Excel Programming |