Auto_Open vs WorkBook_Open

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
Hello all,

We are working on a generic custom command bar which could conceivably be used in multiple places over time. Right now it consists of a class to define the ToolBars and a module to handle the OnAction events of the same.

I would like to instantiate the class in the module with the OnAction code by using Auto_Open and Auto_Close instead of instantiating it in the Workbook_Open and Workbook_BeforeClose events. This does at least 3 things:

A. It gives me a reference to the class in the standard module in case I need to interact with it from the OnAction methods
B. The implementation is cleaner because I don't need to mess with ThisWorkBook code, I can just import the class and module noted above
C. It allows for better encapsulation because all of the code is in the same two places every time

The only events I'm worried about in the class are Workbook New, Activate and Deactivate so I can hide/unhide the ToolBar as appropriate. Does anyone see a problem with this approach? In this context I believe that these methods should be interchangable with each other, but I wanted to check with the gurus to make sure I wasn't missing something.

Thanks in advance for any ideas and/or suggestions!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
I think overall you will find WorkBook_Open to be more robust... Here are a few limitations. There are others but I will defer to our MVPs


An Auto_Open macro is ignored when a workbook is opened programmatically by using the Open method.
An Auto_Open macro runs before any other workbooks are opened. Therefore, if you record actions you want Excel to perform on the default Book1 workbook or a workbook loaded from the XLStart folder, the Auto_Open macro will fail when you restart Excel because it runs before the default and start-up workbooks are opened.
 

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
Thanks very much to both of you! PennySaver, that link was very clear and concise and answered most of my questions.
 

Forum statistics

Threads
1,136,994
Messages
5,679,030
Members
419,800
Latest member
spvsr999

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top