There are two ways to run a macro when a workbook is opened; the Workbook_Open event and an Auto_Open macro.
The Workbook_Open event resides in the ThisWorkbook module. The Auto_Open macro belongs in a standard module. If you have both, the event procedure will fire before the Auto_Open procedure.
Other than their location, the primary difference between the two is what happens when you open a workbook via code. The event will still fire, the Auto_Open macro will not. VBA provides a RunAutoMacros method of the workbook object that allows you to run the Auto_Open code when a workbook is opened through VBA. Here’s an example that demonstrates how to use that method.
Dim wb As Workbook
‘event code will fire on this line
Set wb = Workbooks.Open(sPath & “WorkbookOpen.xls”)
‘You need this line to run Auto_Open
These two examples show the syntax for the event procedure (which goes in ThisWorkbook) and the Auto_Open (which goes in a standard module), but they don’t anything except show a message box.
Private Sub Workbook_Open()
MsgBox “Workbook Open Event”
You don’t need to put your code directly in the events. You create whatever code you need in a standard module and call that code from the event. The same goes for the Auto_Open. If you ever wanted to switch from one method to the other, you might be happy to do it that way. I generally do it when the code is sufficiently complex, but for simply Open events, I don’t.
Now you probably want to know which one to choose. I don’t know, leave me a comment on which you use and why. I use the Workbook_Open event almost exclusively. The Auto_Open is the “old” way of doing it. I see plenty of very experienced coders using Auto_Open, though, so there may be some advantage. If there is, I don’t know it.