Auto_Open vs WorkBook_Open

[sbendbuckeye]

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 Autpen 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!

 

[Oorang]

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 Autpen macro is ignored when a workbook is opened programmatically by using the Open method.
An Autpen 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 Autpen macro will fail when you restart Excel because it runs before the default and start-up workbooks are opened.

 

[Smitty]

See: http://www.excelforum.com/showthread.php?t=361103

HTH,

Smitty

 

[sbendbuckeye]

Thanks very much to both of you! PennySaver, that link was very clear and concise and answered most of my questions.