Avoid unloading of .xla addins when closing books are cancelled.

I have developed .xla addins.
After installing the addin,
run Excel.exe and add a new workbook and add a change to the workbook,
and click 'x' icon on the top right of the Excel window.
Then I'm asked whether I save the change or cancel the closing operation.
I clicked 'Cancel', then , all the workbooks are not closed , however ,
..xla addins are gone.
How can I avoid this?

To test the phenomenan,
I inserted the code below into 'Module1's of those addins.

sub auto_open()
MsgBox ThisWorkBook.FullName & " is opened."
end sub

sub auto_close()
MsgBox ThisWorkBook.FullName & " is closed."
end sub

Zoo,

I use the WorkbookDeactivate event instead. This is an event procedure that
must be created in the ThisWorkbook module. To do so, open the ThisWorkbook
module. In the dropdown at the left top of the main code pain, choose
"Workbook" and in the top right dropdown, choose "Deactivate." Then place
your addin-closing code in the newly-created Deactivate module.

hth,

Doug


"Zoo" <[email protected]> wrote in message
news:[email protected]...
>I have developed .xla addins.
> After installing the addin,
> run Excel.exe and add a new workbook and add a change to the workbook,
> and click 'x' icon on the top right of the Excel window.
> Then I'm asked whether I save the change or cancel the closing operation.
> I clicked 'Cancel', then , all the workbooks are not closed , however ,
> .xla addins are gone.
> How can I avoid this?
>
> To test the phenomenan,
> I inserted the code below into 'Module1's of those addins.
>
> sub auto_open()
> MsgBox ThisWorkBook.FullName & " is opened."
> end sub
>
> sub auto_close()
> MsgBox ThisWorkBook.FullName & " is closed."
> end sub
>
>
>

Thank you so much , Doug.
The problem is solved.
I've never known 'DeActivate' event.

"Doug Glancy" <[email protected]> wrote in message
news:[email protected]...
> Zoo,
>
> I use the WorkbookDeactivate event instead. This is an event procedure
that
> must be created in the ThisWorkbook module. To do so, open the
ThisWorkbook
> module. In the dropdown at the left top of the main code pain, choose
> "Workbook" and in the top right dropdown, choose "Deactivate." Then
place
> your addin-closing code in the newly-created Deactivate module.
>
> hth,
>
> Doug
>
>
> "Zoo" <[email protected]> wrote in message
> news:[email protected]...
> >I have developed .xla addins.
> > After installing the addin,
> > run Excel.exe and add a new workbook and add a change to the workbook,
> > and click 'x' icon on the top right of the Excel window.
> > Then I'm asked whether I save the change or cancel the closing
operation.
> > I clicked 'Cancel', then , all the workbooks are not closed , however ,
> > .xla addins are gone.
> > How can I avoid this?
> >
> > To test the phenomenan,
> > I inserted the code below into 'Module1's of those addins.
> >
> > sub auto_open()
> > MsgBox ThisWorkBook.FullName & " is opened."
> > end sub
> >
> > sub auto_close()
> > MsgBox ThisWorkBook.FullName & " is closed."
> > end sub
> >
> >
> >
>
>

Doug,

I just tested this.
But faik the events: Workbook_Activate and Workbook_Deactivate
will NOT fire for a workbook where IsAddin = true.

The exception:
the DEACTIVATE event will fire if you
explicitly call the ACTIVATE method.
(makes sense: windows for an addin are hidden.)

Private Sub Workbook_Activate()
MsgBox "Activate"
End Sub

Private Sub Workbook_Deactivate()
MsgBox "DeActivate"
End Sub

Sub Test()
ThisWorkbook.Activate
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Doug Glancy wrote in <news:<[email protected]>

> Zoo,
>
> I use the WorkbookDeactivate event instead. This is an event
> procedure that must be created in the ThisWorkbook module. To do so,
> open the ThisWorkbook module. In the dropdown at the left top of the
> main code pain, choose "Workbook" and in the top right dropdown,
> choose "Deactivate." Then place your addin-closing code in the
> newly-created Deactivate module.
>
> hth,
>
> Doug
>
>
> "Zoo" <[email protected]> wrote in message
> news:[email protected]...
> > I have developed .xla addins.
> > After installing the addin,
> > run Excel.exe and add a new workbook and add a change to the
> > workbook, and click 'x' icon on the top right of the Excel window.
> > Then I'm asked whether I save the change or cancel the closing
> > operation. I clicked 'Cancel', then , all the workbooks are not
> > closed , however , .xla addins are gone.
> > How can I avoid this?
> >
> > To test the phenomenan,
> > I inserted the code below into 'Module1's of those addins.
> >
> > sub auto_open()
> > MsgBox ThisWorkBook.FullName & " is opened."
> > end sub
> >
> > sub auto_close()
> > MsgBox ThisWorkBook.FullName & " is closed."
> > end sub
> >
> >
> >

Hey KeepITcool,

I was assuming that Zoo was loading an addin when a wb was opened or closed,
so the event would be for the wb with the associated addin, not the addin
itself.

Thanks,

Doug


"keepITcool" <[email protected]> wrote in message
news:[email protected]...
> Doug,
>
> I just tested this.
> But faik the events: Workbook_Activate and Workbook_Deactivate
> will NOT fire for a workbook where IsAddin = true.
>
> The exception:
> the DEACTIVATE event will fire if you
> explicitly call the ACTIVATE method.
> (makes sense: windows for an addin are hidden.)
>
> Private Sub Workbook_Activate()
> MsgBox "Activate"
> End Sub
>
> Private Sub Workbook_Deactivate()
> MsgBox "DeActivate"
> End Sub
>
> Sub Test()
> ThisWorkbook.Activate
> End Sub
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Doug Glancy wrote in <news:<[email protected]>
>
>> Zoo,
>>
>> I use the WorkbookDeactivate event instead. This is an event
>> procedure that must be created in the ThisWorkbook module. To do so,
>> open the ThisWorkbook module. In the dropdown at the left top of the
>> main code pain, choose "Workbook" and in the top right dropdown,
>> choose "Deactivate." Then place your addin-closing code in the
>> newly-created Deactivate module.
>>
>> hth,
>>
>> Doug
>>
>>
>> "Zoo" <[email protected]> wrote in message
>> news:[email protected]...
>> > I have developed .xla addins.
>> > After installing the addin,
>> > run Excel.exe and add a new workbook and add a change to the
>> > workbook, and click 'x' icon on the top right of the Excel window.
>> > Then I'm asked whether I save the change or cancel the closing
>> > operation. I clicked 'Cancel', then , all the workbooks are not
>> > closed , however , .xla addins are gone.
>> > How can I avoid this?
>> >
>> > To test the phenomenan,
>> > I inserted the code below into 'Module1's of those addins.
>> >
>> > sub auto_open()
>> > MsgBox ThisWorkBook.FullName & " is opened."
>> > end sub
>> >
>> > sub auto_close()
>> > MsgBox ThisWorkBook.FullName & " is closed."
>> > end sub
>> >
>> >
>> >