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,
NOTE for MacOS Users: To begin recording a macro, on the Status Bar,
Step2. To complete the setup for this Macro, input the following data into the appropriate areas in the Record Macro dialog box:
|Dialog Box Labels||Data to Enter|
|Store macro in:||This Workbook|
|Description||Automatically fit columns to their data when a workbook is opened|
Step3. To begin recording the macro,
Step4. To stop the recorder, in the status bar,
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.
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.
|Application||Tells Excel we're going to ask it to do something.|
|.Run||The action we are asking Excel to perform. In this case, we're asking Excel to run the following macro.|
|"AutoFit"||The name of the macro we would like Excel to run. The macro name must be enclosed within quotation marks.|
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.
Step8. To switch to the 2006 worksheet,
Click the 2006 worksheet tab
Step9. To save the workbook, press:
Step10. To close the workbook,
Click File, Click Close
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...
Step12. If necessary, navigate to the epclass folder.
Step13. To open the file,
NOTE for MacOS Users: You will see a security warning that the workbook contains macros: