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