Custom Addin, Excel Shutdown Problem

[RodT]

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.

 

[Rob Bovey]

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 *

 

[Rod T]

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.

 

[Rob Bovey]

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 *

 

[Rob Bovey]

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 *