Using an Auto_Open Macro

Not only can a macro be run from the macro menu, or a button on the Quick Access Toolbar, Excel also allows you to run a macro automatically when a workbook opens. These types of macros are called Auto_Open macros. Auto_Open is the name the macro must have in order to run when a workbook is opened.

We have a couple choices here. We could just rename the macro we've already recorded as Auto_Open, or we could create a new macro named Auto_Open and have that macro run our AutoFit macro. We will be using the second option today.

Step1. To begin recording a macro, on the Status Bar,

Click

NOTE for MacOS Users: To begin recording a macro, on the Status Bar,
Click.

We see the Record Macro dialog box.

Step2. To complete the setup for this Macro, input the following data into the appropriate areas in the Record Macro dialog box:

We're ready to begin recording.

Step3. To begin recording the macro,

Click

The only action we want this macro to perform is to run our AutoFit macro. This cannot be accomplished by recording. Instead, we will have to add a single line of code to our Auto_Open macro that tells Excel to run AutoFit.

Since there are no actions that we can record, let's stop then edit the macro.

Step4. To stop the recorder, in the status bar,

Click

Macro recording is stopped.

The code we need to add is relatively simple, so let's open the macro to edit, make the change, then close the Visual Basic Editor.

Step5. To edit the macro, in the Ribbon, on the View tab,

Click, Click Auto_Open, Click

NOTE for MacOS Users: To edit the macro code, in the Ribbon, on the View tab, Click, Click Auto_Open, Click.

The macro opens in the Visual Basic Editor. Let's add the code that runs the AutoFit macro now.

Step6. To add the code that will run the AutoFit macro in the Auto_Open macro, type the code shown in bold below:

Sub Auto_Open()
'
'Auto_Open Macro
'Automatically fit cells to their data when a workbook is opened.
Application.Run "AutoFit"
End Sub
    

Exiting code block.

Here's a brief explanation of what we just wrote:

In this statement, if you read from right to left, it reads as follows: "Hey, Excel (Application). Would you run the macro (.Run) named AutoFit ("AutoFit")?" This code can be used to run any macro available to a workbook by changing the name of the macro inside the quotation marks.

We can now close the Visual Basic Editor and return to our workbook.

Step7. Close the Visual Basic Editor.

NOTE: Alternatively, you can use the Window menu in the Menu bar to switch back to the workbook and leave the Visual Basic Editor open.

Let's test this macro on the next worksheet in our workbook. Since this macro runs when we open the workbook, we have to select the worksheet we want to test the macro on before we save and close. That way it'll be the active worksheet when the file is opened next time.

Step8. To switch to the 2006 worksheet,

Click the 2006 worksheet tab

Let's save, close, and re-open the workbook to test the Auto_Open macro's functionality.

Step9. To save the workbook, press:

Control key+s

The workbook is saved.

Step10. To close the workbook,

Click File, Click Close

We see an empty Excel work space.

NOTE for MacOS Users: Excel can still be running even if all workbooks have been closed. Look in the Menu bar or in the Dock to be certain.

Step11. To re-open the workbook,

Click File, Click Open...

We see the Open File dialog box.

Step12. If necessary, navigate to the epclass folder.

Step13. To open the file,

Double-Click macroex.xlsm

You will notice that nothing has happened yet, but you will see a security warning below the ribbon:

NOTE for MacOS Users: You will see a security warning that the workbook contains macros:

Nothing has happened because, by default, macro-enabled workbooks open with all macro content disabled. The security warning will allow us to enable this content.