I have an add-in in which I have an Auto_Open event which creates a
custom menu item on the Worksheet Menu Bar to run other code in the
add-in. I was reading some stuff here in the NG about the AddinInstall
event and was wondering if that would be the more appropriate event to
use. Would using AddinInstall cause the custom menu to automatically
appear when the add-in is installed? Would the AddinUninstall event
work similarly?

Bottom line...to be "clean" I would like to have the custom menu item
to appear when the add-in in installed via ToolsAdd-ins and disappear
when the add-in in uninstalled via ToolsAdd-ins if this is even
possible.

Thanks for the help.

Mike.

Why not use the 'Workbook_Open' procedure of the ThisWorkbook Object of your
add-in to create your menu and the 'Workbook_BeforeClose' procedure to delete
your menu?
HTH,
Gary Brown

"Michael Malinsky" wrote:

I have an add-in in which I have an Auto_Open event which creates a
custom menu item on the Worksheet Menu Bar to run other code in the
add-in. I was reading some stuff here in the NG about the AddinInstall
event and was wondering if that would be the more appropriate event to
use. Would using AddinInstall cause the custom menu to automatically
appear when the add-in is installed? Would the AddinUninstall event
work similarly?

Bottom line...to be "clean" I would like to have the custom menu item
to appear when the add-in in installed via ToolsAdd-ins and disappear
when the add-in in uninstalled via ToolsAdd-ins if this is even
possible.

Thanks for the help.

Mike.

"Michael Malinsky" wrote in message
oups.com...
I have an add-in in which I have an Auto_Open event which creates a
custom menu item on the Worksheet Menu Bar to run other code in the
add-in. I was reading some stuff here in the NG about the AddinInstall
event and was wondering if that would be the more appropriate event to
use. Would using AddinInstall cause the custom menu to automatically
appear when the add-in is installed? Would the AddinUninstall event
work similarly?

Bottom line...to be "clean" I would like to have the custom menu item
to appear when the add-in in installed via ToolsAdd-ins and disappear
when the add-in in uninstalled via ToolsAdd-ins if this is even
possible.

Hi Mike,

For the purposes of adding and removing custom menus for an add-in there
is no practical difference between the Auto_Open/Auto_Close procedures, the
Workbook_AddinInstall/Workbook_AddinUninstall events or the
Workbook_Open/Workbook_BeforeClose events. It's just a matter of preference.

I use Auto_Open/Auto_Close in all of my add-ins to avoid putting any
code behind the ThisWorkbook object. If that code ever becomes corrupted you
may have to rebuild the whole workbook, so I do my best to leave that module
empty.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Rob,
The reason I use Workbook_Open is because a wise man once said...
Newsgroups: microsoft.public.excel.programming
From: "Rob Bovey"
Local: Tues, Jan 15 2002 12:02 pm
Subject: Number of Auto_Open (Auto_Close) Events
"There's no problem with the concept, but Auto_Open and Auto_Close
procedures will not fire automatically in a workbook that's been
opened/closed from VBA."
That got me thinking so I changed to the 'Workbook_Open' and
Workbook_BeforeClose' procedures.
Question: Is there more of a chance that the ThisWorkbook object will
get corrupted than a module?
Question: Are you recommending the Auto_Open over Workbook_Open?
Thanks for your insights in advance. Always very interested in your
recommendations.
Sincerely,
Gary Brown


"Rob Bovey" wrote:

Hi Mike,

For the purposes of adding and removing custom menus for an add-in there
is no practical difference between the Auto_Open/Auto_Close procedures, the
Workbook_AddinInstall/Workbook_AddinUninstall events or the
Workbook_Open/Workbook_BeforeClose events. It's just a matter of preference.

I use Auto_Open/Auto_Close in all of my add-ins to avoid putting any
code behind the ThisWorkbook object. If that code ever becomes corrupted you
may have to rebuild the whole workbook, so I do my best to leave that module
empty.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Hi Gary,

The reason I use Workbook_Open is because a wise man once said...

I'd have to go back and check out the context of that conversation, but
yeah, it's absolutely correct that Auto_Open won't fire when you open a
workbook from VBA whereas all the event procedures will.

However, it's pretty trivial to manually fire the Auto_Open procedure
for a workbook opened via VBA using the Workbook.RunAutoMacros method. And
for me, at least, it's rare that I want anything in a workbook firing on its
own when I open it from VBA, so this is typically the route I use (or if I
control the code in the workbook being opened I create a special startup
procedure that I call using Application.Run when I'm ready for it to fire).

Question: Is there more of a chance that the ThisWorkbook object will
get corrupted than a module?

I wouldn't say there's more of a chance that one will get corrupted
faster than the other. The critical point is that if a regular code module
becomes corrupted, fixing it is trivial, whereas if the code-behind class
module for the ThisWorkbook object becomes corrupted there's not much you
can do short of rebuilding the workbook.

Question: Are you recommending the Auto_Open over Workbook_Open?

Yes, there just aren't any significant advantages to using Workbook_Open
over Auto_Open. The case for Auto_Close vs. Workbook_BeforeClose is a bit
more complicated because Workbook_BeforeClose does give you some options
that Auto_Close doesn't. If I do decide to use Workbook_BeforeClose, though,
I put it in a WithEvents class module, not directly behind the ThisWorkbook
object.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Gary Brown" wrote in message
...
Rob,
The reason I use Workbook_Open is because a wise man once said...
Newsgroups: microsoft.public.excel.programming
From: "Rob Bovey"
Local: Tues, Jan 15 2002 12:02 pm
Subject: Number of Auto_Open (Auto_Close) Events
"There's no problem with the concept, but Auto_Open and Auto_Close
procedures will not fire automatically in a workbook that's been
opened/closed from VBA."
That got me thinking so I changed to the 'Workbook_Open' and
Workbook_BeforeClose' procedures.
Question: Is there more of a chance that the ThisWorkbook object will
get corrupted than a module?
Question: Are you recommending the Auto_Open over Workbook_Open?
Thanks for your insights in advance. Always very interested in your
recommendations.
Sincerely,
Gary Brown


"Rob Bovey" wrote:

Hi Mike,

For the purposes of adding and removing custom menus for an add-in
there
is no practical difference between the Auto_Open/Auto_Close procedures,
the
Workbook_AddinInstall/Workbook_AddinUninstall events or the
Workbook_Open/Workbook_BeforeClose events. It's just a matter of
preference.

I use Auto_Open/Auto_Close in all of my add-ins to avoid putting any
code behind the ThisWorkbook object. If that code ever becomes corrupted
you
may have to rebuild the whole workbook, so I do my best to leave that
module
empty.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Thanks, Rob. I used the Auto_Open/Auto_Close procedures. I did change
my code to use the Workbook_AddinInstall/Workbook_AddinUninstall, but
when I unselected the add-in from ToolsAdd-ins, nothing happened
unless I did something wrong. Now, with the Auto_Open/Auto_Close, the
menu items appears/disappears as I was hoping I could do.

Thanks again,
Mike.

"Michael Malinsky" wrote in message
oups.com...
Thanks, Rob. I used the Auto_Open/Auto_Close procedures. I did change
my code to use the Workbook_AddinInstall/Workbook_AddinUninstall, but
when I unselected the add-in from ToolsAdd-ins, nothing happened
unless I did something wrong. Now, with the Auto_Open/Auto_Close, the
menu items appears/disappears as I was hoping I could do.

Hi Mike,

Those events definitely should have fired. The first culprit I'd check
for is if you might have set Application.EnableEvents = False at some point
without also setting it back to True.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Thanks Rob,
Really appreciate the feedback. That's one thing I love about this forum.
You learn something new and/or get reminded of something virtually every day
from great people!
Have a good one.
Gary Brown


"Rob Bovey" wrote:

Hi Gary,

The reason I use Workbook_Open is because a wise man once said...

I'd have to go back and check out the context of that conversation, but
yeah, it's absolutely correct that Auto_Open won't fire when you open a
workbook from VBA whereas all the event procedures will.

However, it's pretty trivial to manually fire the Auto_Open procedure
for a workbook opened via VBA using the Workbook.RunAutoMacros method. And
for me, at least, it's rare that I want anything in a workbook firing on its
own when I open it from VBA, so this is typically the route I use (or if I
control the code in the workbook being opened I create a special startup
procedure that I call using Application.Run when I'm ready for it to fire).

Question: Is there more of a chance that the ThisWorkbook object will
get corrupted than a module?

I wouldn't say there's more of a chance that one will get corrupted
faster than the other. The critical point is that if a regular code module
becomes corrupted, fixing it is trivial, whereas if the code-behind class
module for the ThisWorkbook object becomes corrupted there's not much you
can do short of rebuilding the workbook.

Question: Are you recommending the Auto_Open over Workbook_Open?

Yes, there just aren't any significant advantages to using Workbook_Open
over Auto_Open. The case for Auto_Close vs. Workbook_BeforeClose is a bit
more complicated because Workbook_BeforeClose does give you some options
that Auto_Close doesn't. If I do decide to use Workbook_BeforeClose, though,
I put it in a WithEvents class module, not directly behind the ThisWorkbook
object.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Gary Brown" wrote in message
...
Rob,
The reason I use Workbook_Open is because a wise man once said...
Newsgroups: microsoft.public.excel.programming
From: "Rob Bovey"
Local: Tues, Jan 15 2002 12:02 pm
Subject: Number of Auto_Open (Auto_Close) Events
"There's no problem with the concept, but Auto_Open and Auto_Close
procedures will not fire automatically in a workbook that's been
opened/closed from VBA."
That got me thinking so I changed to the 'Workbook_Open' and
Workbook_BeforeClose' procedures.
Question: Is there more of a chance that the ThisWorkbook object will
get corrupted than a module?
Question: Are you recommending the Auto_Open over Workbook_Open?
Thanks for your insights in advance. Always very interested in your
recommendations.
Sincerely,
Gary Brown


"Rob Bovey" wrote:

Hi Mike,

For the purposes of adding and removing custom menus for an add-in
there
is no practical difference between the Auto_Open/Auto_Close procedures,
the
Workbook_AddinInstall/Workbook_AddinUninstall events or the
Workbook_Open/Workbook_BeforeClose events. It's just a matter of
preference.

I use Auto_Open/Auto_Close in all of my add-ins to avoid putting any
code behind the ThisWorkbook object. If that code ever becomes corrupted
you
may have to rebuild the whole workbook, so I do my best to leave that
module
empty.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Rob,

It wasn't the Application.EnableEvents that caused it since I didn't
use it (or have never used it as I can recall). I probably used
incorrect syntax so it wasn't recognized as being an event that should
have fired. I may try it again since I'm now curious as to why it
didn't work, but since it DOES work, maybe I won't mess with it.

Thanks again for the help.