Run a Macro When Opening a Workbook

There may be a time when you want a macro to run automatically every time you open a workbook.  Excel has a built in way to help with this – the Auto_Open subroutine (Workbook_Open achieves a similar result).  Each time Excel opens, Auto_Open() will run only once when the workbook is open.  Let’s go ahead and take a look at an example.

Auto_Open Example

Sub Auto_Open()
Msgbox(“Testing Auto_Open”)
End Sub

The is obviously a very simple example, but if you place this code in your module then save and close your workbook, the feature can then be tested.  Testing is as easy as opening your newly saved workbook.  When the program is open you will see the text you entered: Testing Auto_Open.

Uses for Auto_Open
The number of ways you can use this feature is endless, but here are just a few ways I have used this feature recently:

• Automatically refresh database queries in your workbook upon opening
• Prompt the user for parameters to update your spreadsheet with.
• Automatically update the report with new data to present to the user.

In Summary
Like I said before, Workbook_Open is a similar macro that can be used almost interchangeably with Auto_Open.  Again, I believe the easiest way to become comfortable with writing macros is to record a macro using Excel’s built in recorder and copy that code to where you would like it used (perhaps the Auto_Open feature in the case).

More Excel Tips

• Difference Between Sub and Function