Custom Addin, Excel Shutdown Problem
Hi, This one is probably for a bit of an Excel guru.
I have a custom home made addin installed in Excel2000. I have a workbook which uses functions from the Addin, the workbook also has a combo box which calls one of these functions on it's CHANGE event. If I close the workbook, then close excel everything is fine. However, if I close Excel with the workbook loaded I get a serious error which crashes Excel and causes problems. When I close Excel with the workbook loaded, the Addin WorkBook_BeforeClose event is called, then the loaded WorkBook_BeforeClose event is called, then the combo CHANGE event is called, in this order (for some strange reason). When the combo CHANGE event is called it tries to call the addin function, but of course at this stage the addin has been closed and this causes Excel to crash in an unseemly manner. I notice that some other addins installed by some software stay loaded while this is happening. Why would my Addin be closed BEFORE the workbook is closed ? This appears to be the cause of the problem. Any help appreciated. Rod. |
Custom Addin, Excel Shutdown Problem
Hi Rod,
All add-ins are always shut down before any open workbooks. The only time this might appear to be different is if the add-in is not an add-in but really just a hidden workbook. My advice would be first to stop whatever is causing the combo box change event to fire when your workbook closes. If you can't do that, you can use a flag variable to bypass the event when your workbook is shutting down. It would work something like this (I'm assuming the combo box is on a worksheet): --------------- In Module 1 --------------- Public bShutDown As Boolean ------------------------------------------ In the code module behind ThisWorkbook ------------------------------------------ Private Sub Workbook_BeforeClose(Cancel As Boolean) bShutDown = True ''' Other code End Sub ------------------------------------------ In the code module behind the Worksheet ------------------------------------------ Private Sub ComboBox1_Change() If bShutDown Then Exit Sub ''' Otherwise continue executing. End Sub Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "RodT" wrote in message ... Hi, This one is probably for a bit of an Excel guru. I have a custom home made addin installed in Excel2000. I have a workbook which uses functions from the Addin, the workbook also has a combo box which calls one of these functions on it's CHANGE event. If I close the workbook, then close excel everything is fine. However, if I close Excel with the workbook loaded I get a serious error which crashes Excel and causes problems. When I close Excel with the workbook loaded, the Addin WorkBook_BeforeClose event is called, then the loaded WorkBook_BeforeClose event is called, then the combo CHANGE event is called, in this order (for some strange reason). When the combo CHANGE event is called it tries to call the addin function, but of course at this stage the addin has been closed and this causes Excel to crash in an unseemly manner. I notice that some other addins installed by some software stay loaded while this is happening. Why would my Addin be closed BEFORE the workbook is closed ? This appears to be the cause of the problem. Any help appreciated. Rod. |
Custom Addin, Excel Shutdown Problem
Thanks for the info Rob,
However, I've already tried the shutdown flag trick and it doesn't work. I declared the flag as public in a normal module. I did a step thru when shutting down and I can see the workbook before close event working, I can see the flag set to true, then a pause, then the combo change event gets called, and the variable is false here. I have no idea why the change event would be called on the combo at such a late stage in shutting down. I certainly haven't called it in code anywhere. I would assume that the only thing that could fire the change event would be the user or code changing a value. Just to make life more interesting I've also found this behaviour... If I have the change event in the worksheet class module, even if there are no lines of code in it, it crashes. If I delete the change event, then it closes OK (but the worksheet won't work without the change event), I put the change event back with no code in it, it crashes on closing Excel ?? I have other workbooks with combo boxes and they don't do this. I've gone thru all of the code behind the addin and this workbook and set all of the object variables to "Nothing" before the end of each and every function, just in case. So, it's got me stuffed. -----Original Message----- Hi Rod, All add-ins are always shut down before any open workbooks. The only time this might appear to be different is if the add-in is not an add-in but really just a hidden workbook. My advice would be first to stop whatever is causing the combo box change event to fire when your workbook closes. If you can't do that, you can use a flag variable to bypass the event when your workbook is shutting down. It would work something like this (I'm assuming the combo box is on a worksheet): --------------- In Module 1 --------------- Public bShutDown As Boolean ------------------------------------------ In the code module behind ThisWorkbook ------------------------------------------ Private Sub Workbook_BeforeClose(Cancel As Boolean) bShutDown = True ''' Other code End Sub ------------------------------------------ In the code module behind the Worksheet ------------------------------------------ Private Sub ComboBox1_Change() If bShutDown Then Exit Sub ''' Otherwise continue executing. End Sub Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "RodT" wrote in message ... Hi, This one is probably for a bit of an Excel guru. I have a custom home made addin installed in Excel2000. I have a workbook which uses functions from the Addin, the workbook also has a combo box which calls one of these functions on it's CHANGE event. If I close the workbook, then close excel everything is fine. However, if I close Excel with the workbook loaded I get a serious error which crashes Excel and causes problems. When I close Excel with the workbook loaded, the Addin WorkBook_BeforeClose event is called, then the loaded WorkBook_BeforeClose event is called, then the combo CHANGE event is called, in this order (for some strange reason). When the combo CHANGE event is called it tries to call the addin function, but of course at this stage the addin has been closed and this causes Excel to crash in an unseemly manner. I notice that some other addins installed by some software stay loaded while this is happening. Why would my Addin be closed BEFORE the workbook is closed ? This appears to be the cause of the problem. Any help appreciated. Rod. . |
Custom Addin, Excel Shutdown Problem
Hi Rod,
Two more suggestions. First, try deleting the combo box itself, save and close the workbook, then reopen the workbook and add a new combo box. There may be something wrong with this control. Second, if you don't really need an ActiveX control (and in many many applications I see, they really aren't needed), try using the combo box control from the Forms toolbar. There are no events associated with this, just a procedure you assign via the OnAction property, so this is very likely to solve the problem. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Rod T" wrote in message ... Thanks for the info Rob, However, I've already tried the shutdown flag trick and it doesn't work. I declared the flag as public in a normal module. I did a step thru when shutting down and I can see the workbook before close event working, I can see the flag set to true, then a pause, then the combo change event gets called, and the variable is false here. I have no idea why the change event would be called on the combo at such a late stage in shutting down. I certainly haven't called it in code anywhere. I would assume that the only thing that could fire the change event would be the user or code changing a value. Just to make life more interesting I've also found this behaviour... If I have the change event in the worksheet class module, even if there are no lines of code in it, it crashes. If I delete the change event, then it closes OK (but the worksheet won't work without the change event), I put the change event back with no code in it, it crashes on closing Excel ?? I have other workbooks with combo boxes and they don't do this. I've gone thru all of the code behind the addin and this workbook and set all of the object variables to "Nothing" before the end of each and every function, just in case. So, it's got me stuffed. -----Original Message----- Hi Rod, All add-ins are always shut down before any open workbooks. The only time this might appear to be different is if the add-in is not an add-in but really just a hidden workbook. My advice would be first to stop whatever is causing the combo box change event to fire when your workbook closes. If you can't do that, you can use a flag variable to bypass the event when your workbook is shutting down. It would work something like this (I'm assuming the combo box is on a worksheet): --------------- In Module 1 --------------- Public bShutDown As Boolean ------------------------------------------ In the code module behind ThisWorkbook ------------------------------------------ Private Sub Workbook_BeforeClose(Cancel As Boolean) bShutDown = True ''' Other code End Sub ------------------------------------------ In the code module behind the Worksheet ------------------------------------------ Private Sub ComboBox1_Change() If bShutDown Then Exit Sub ''' Otherwise continue executing. End Sub Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "RodT" wrote in message ... Hi, This one is probably for a bit of an Excel guru. I have a custom home made addin installed in Excel2000. I have a workbook which uses functions from the Addin, the workbook also has a combo box which calls one of these functions on it's CHANGE event. If I close the workbook, then close excel everything is fine. However, if I close Excel with the workbook loaded I get a serious error which crashes Excel and causes problems. When I close Excel with the workbook loaded, the Addin WorkBook_BeforeClose event is called, then the loaded WorkBook_BeforeClose event is called, then the combo CHANGE event is called, in this order (for some strange reason). When the combo CHANGE event is called it tries to call the addin function, but of course at this stage the addin has been closed and this causes Excel to crash in an unseemly manner. I notice that some other addins installed by some software stay loaded while this is happening. Why would my Addin be closed BEFORE the workbook is closed ? This appears to be the cause of the problem. Any help appreciated. Rod. . |
Custom Addin, Excel Shutdown Problem
Hi Rod,
One more thing I overlooked related to the global flag being False when the combo box change event fires. Make sure you aren't using End (not End Sub or End Function, just "End") anywhere in your code. If end gets called somewhere in the Workbook_BeforeClose event, for example, it would reset all your public variables, including the bShutDown variable. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Rob Bovey" wrote in message ... Hi Rod, Two more suggestions. First, try deleting the combo box itself, save and close the workbook, then reopen the workbook and add a new combo box. There may be something wrong with this control. Second, if you don't really need an ActiveX control (and in many many applications I see, they really aren't needed), try using the combo box control from the Forms toolbar. There are no events associated with this, just a procedure you assign via the OnAction property, so this is very likely to solve the problem. -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Rod T" wrote in message ... Thanks for the info Rob, However, I've already tried the shutdown flag trick and it doesn't work. I declared the flag as public in a normal module. I did a step thru when shutting down and I can see the workbook before close event working, I can see the flag set to true, then a pause, then the combo change event gets called, and the variable is false here. I have no idea why the change event would be called on the combo at such a late stage in shutting down. I certainly haven't called it in code anywhere. I would assume that the only thing that could fire the change event would be the user or code changing a value. Just to make life more interesting I've also found this behaviour... If I have the change event in the worksheet class module, even if there are no lines of code in it, it crashes. If I delete the change event, then it closes OK (but the worksheet won't work without the change event), I put the change event back with no code in it, it crashes on closing Excel ?? I have other workbooks with combo boxes and they don't do this. I've gone thru all of the code behind the addin and this workbook and set all of the object variables to "Nothing" before the end of each and every function, just in case. So, it's got me stuffed. -----Original Message----- Hi Rod, All add-ins are always shut down before any open workbooks. The only time this might appear to be different is if the add-in is not an add-in but really just a hidden workbook. My advice would be first to stop whatever is causing the combo box change event to fire when your workbook closes. If you can't do that, you can use a flag variable to bypass the event when your workbook is shutting down. It would work something like this (I'm assuming the combo box is on a worksheet): --------------- In Module 1 --------------- Public bShutDown As Boolean ------------------------------------------ In the code module behind ThisWorkbook ------------------------------------------ Private Sub Workbook_BeforeClose(Cancel As Boolean) bShutDown = True ''' Other code End Sub ------------------------------------------ In the code module behind the Worksheet ------------------------------------------ Private Sub ComboBox1_Change() If bShutDown Then Exit Sub ''' Otherwise continue executing. End Sub Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "RodT" wrote in message ... Hi, This one is probably for a bit of an Excel guru. I have a custom home made addin installed in Excel2000. I have a workbook which uses functions from the Addin, the workbook also has a combo box which calls one of these functions on it's CHANGE event. If I close the workbook, then close excel everything is fine. However, if I close Excel with the workbook loaded I get a serious error which crashes Excel and causes problems. When I close Excel with the workbook loaded, the Addin WorkBook_BeforeClose event is called, then the loaded WorkBook_BeforeClose event is called, then the combo CHANGE event is called, in this order (for some strange reason). When the combo CHANGE event is called it tries to call the addin function, but of course at this stage the addin has been closed and this causes Excel to crash in an unseemly manner. I notice that some other addins installed by some software stay loaded while this is happening. Why would my Addin be closed BEFORE the workbook is closed ? This appears to be the cause of the problem. Any help appreciated. Rod. . |
All times are GMT +1. The time now is 09:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com