How to Run a Macro Automatically When a Workbook Opens

How to run a macro automatically when a workbook opens

To run a macro automatically when a workbook opens, add a Workbook_Open event to the ThisWorkbook module and save the file as .xlsm. Excel runs whatever is inside that event every time the file is opened, with no button to click.

The confusing part is that there are two ways to do this and the internet is split between them. One is what Microsoft documents. The other is a leftover from much older Excel.

TL;DR: Key takeaways

• The code goes in ThisWorkbook, not in a normal module.
• Pick Workbook from the Object dropdown and Excel writes the empty event for you.
• Save as .xlsm, or the code is discarded.
• Auto_Open is the legacy equivalent in a standard module. Use Workbook_Open.
• It only runs if the person opening the file allows macros, so never rely on it silently.

Add the Workbook_Open event

Microsoft's steps are short, and the third one is the part people miss.

1. Open the workbook, then press Alt + F11 to open the VBA editor.
2. In the Project Explorer, right-click ThisWorkbook and choose View Code.
3. In the Object list above the code window, select Workbook.
4. Excel creates an empty Private Sub Workbook_Open() procedure. Put your code inside it.
5. Save the workbook as .xlsm, then close and reopen it to test.

Step 3 is what generates the event. Typing the procedure by hand works too, but only if it sits in the ThisWorkbook module. The same code pasted into a normal module does nothing at all, which is the single most common reason this fails.

If the Project Explorer is not visible, use View > Project Explorer to bring it back.

Workbook_Open or Auto_Open?

Both run code when a file opens, and old forum answers use them interchangeably. They are not the same thing.

Workbook_Open is an event procedure. It belongs in the ThisWorkbook module, and it is what Microsoft's current documentation uses.

Auto_Open is a plain macro named Auto_Open sitting in a standard module. It dates from much older versions of Excel and survives mainly in inherited workbooks and decade-old forum threads.

Use Workbook_Open. If you are troubleshooting a workbook that does something unexpected on open and you cannot find the cause, check both places, because whoever built it may have used either.

It only runs if macros are allowed

An automatic macro is still a macro. Nothing runs until the person opening the file lets it.

If the workbook was downloaded, Excel may block it outright, and your Workbook_Open code never fires. That is not a bug in the code. Enabling macros when there is no enable button covers clearing that block.

This matters if you are building something for other people. A file that quietly depends on startup code will look broken to anyone who declines the prompt, so design it to fail visibly rather than silently.

Opening the file without triggering it

Sometimes you need to get into a workbook without its startup code running, usually to edit or remove it.

The dependable way is to decline macros at the prompt. Open the file, leave macros disabled, and the event will not fire, but you can still reach the code in the editor to change it.

Holding Shift while a workbook opens is the trick most often suggested for this, and it is worth knowing, though reports of it failing on particular setups are common enough that it is not something to rely on. If you need certainty, decline the macro prompt.

Frequently asked questions

Where does Workbook_Open code go?

In the ThisWorkbook module, reachable by right-clicking ThisWorkbook in the VBA editor's Project Explorer and choosing View Code. Placed in a normal module it will never run, which is the usual cause when auto-run code appears to do nothing.

Why is my Workbook_Open macro not running?

Three usual causes. The code is in a normal module instead of ThisWorkbook, the file was saved as .xlsx so the code was discarded, or macros were blocked when the file opened. Check them in that order.

What is the difference between Workbook_Open and Auto_Open?

Workbook_Open is an event procedure in the ThisWorkbook module and is what Microsoft documents today. Auto_Open is an older plain macro in a standard module, kept for backward compatibility. Prefer Workbook_Open in anything new.

How do I stop a macro from running when a file opens?

Decline macros at the security prompt. The workbook still opens and you can edit or delete the startup code in the VBA editor, but nothing runs. Then save as .xlsm once the code is gone.

Can a workbook run a macro automatically in Excel for the web?

No. The browser version cannot run VBA at all, so a Workbook_Open event is simply ignored there. The file opens and the data is visible, but the startup code does nothing until it is opened in desktop Excel.

What to do next

Startup code is the least visible way to trigger a macro, which makes it the easiest to forget about later. Add a comment explaining what it does, for whoever inherits the file. For triggers people can see and choose, a button or keyboard shortcut is usually the better design, and deleting or editing a macro covers removing startup code you no longer want.