I’ve seen it written that
Auto_Open() was superseded by
Workbook_Open() and that it is therefore redundant and only still available for backward compatibility. In fact, there are subtle differences between the two which means that, in my opinion,
Auto_Open() remains a distinct and useful tool in the Excel developer toolkit. Here are the differences I know about:
The Code Container
While this difference isn’t significant, it does catch out beginners.
Auto_Open() must sit in a standard code module whereas
Workbook_Open() must be housed in the
ThisWorkbook class module. If either one is in the wrong place then it won’t run.
Auto_Open() lives in a standard code module, it is possible to generate it using the macro recorder. By contrast,
Workbook_Open() cannot be generated by the macro recorder in situ although you could generate it in a standard code module and copy it across [see Patrick’s comment]. For me, the best way to create the
Workbook_Open() procedure stub is to use the two dropdown boxes at the top of the code pane when the
ThisWorkbook code module is active and then to manually code the contents.
- Contrary to popular belief,
Auto_Open()can be declared with a
Privatescope: it does not have to be
Public. Setting the scope to
Privatewill hide it from the Assign Macro dialog box’s available macro list and make it inaccessible to other code modules.
This is a really important difference. If
Application.EnableEvents is set to
Workbook_Open() does not get triggered. There’s a lot of sloppy VBA code around which sets
False but fails to set it back to
True again, which means your
Workbook_Open() code is vulnerable to other people’s mistakes.
Auto_Open() is unaffected by
Application.EnableEvents so some developers use
Auto_Open() if they want to ensure that a certain piece of code is executed when a user opens the workbook regardless of the
Application.EnableEvents state. This is, of course, subject to user macro security settings.
- Holding down the SHIFT key when opening a workbook will disable both
End User Or Automation?
Another really important difference. If a workbook is opened programmatically then
Auto_Open() does not get triggered whereas
Workbook_Open() does. This means that
Auto_Open() is an easy way to segregate code which should only be executed for end-users and not during automation.
- If you want to disable
Workbook_Open()during automation then set
Falsebefore opening the workbook.
- If you want to run
Auto_Open()(and other auto methods) during automation then use the
If your workbook has both
Auto_Open() procedures then it’s important to know that it is the
Workbook_Open() method that gets called first.
Auto_Open()are both called before the