You might want to set up application level events in your add-in:
For documentation and some explanation on Application Level Events, you
might want to read Chip Pearson's site
http://www.cpearson.com/excel/appevent.htm
this reflects the method presented in VBA help, but see this simpler, more
logical method suggested by KeepitCool and Further endorsed by Jamie Collins
and which uses only the Thisworkbook class module (eliminating the need to
instantiate the class and which keeps everything in one place)
[KeepitCool, otherwise known as Jurgen Volkerink @
www.XLsupport.com ]
Some discussion:
http://groups.google.co.uk/groups?se....microsoft.com
the thread:
http://groups.google.co.uk/groups?th....microsoft.com
Source of article below:
http://groups.google.co.uk/groups?se....microsoft.com
From: keepITcool )
Subject: Using Events with the Application Object & XL 2003
View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2005-01-23 18:47:05 PST
What Chip doesn't mention and what I find a lot easier...
you dont need a separate class module..
Thisworkbook IS a class module so you can keep all your code
(and the withevents application variable) there.
'thisworkbook code module...
Option Explicit
Private WithEvents XlsEvents As Application
Private Sub Workbook_Open()
Set XlsEvents = Application
End Sub
Private Sub XlsEvents_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You just opened " & Wb.Name
End Sub
--
Regards,
Tom Ogilvy
"mark" wrote in message
...
Thanks Bernie, but I do not think I provided enough information. I have
created an add-in that creates a custom menu and my add-in is dependant on
a
third party add-in that is installed on my workstation. When the third
party
add-in is unloaded by the user selecting tool-add-ins and deselecting the
add-in, I need my add-in to know that this event occurred and change its
properties.
I have an auto_close sub in mine add-in, but when that user deselects the
third party add-in, the auto_close event of the third party add-in gets
called not mine because I placed a break on my auto_close event and it
does
not get called. Any ideas?
"Bernie Deitrick" wrote:
Mark,
Use the Workbook_BeforeClose event of the add-in, or use an Auto_Close()
sub. Deselecting an Add-in closes the file.
HTH,
Bernie
MS Excel MVP
"mark" wrote in message
...
Is there any way to detect when a user deselects an add-in in Excel
using
VBA. I need to be able to trap this type of action and change a
custom
menu
bar that I created. Any assistance would be appreciated.
Thanks!