Excel Macros - Configuring a Macro

You can record a macro and save it with the name Auto_Open to run it whenever you open the workbook that contains this macro.

You can also write VBA code for the same purpose with the Open event of the workbook. The Open event runs the code in the sub procedure Workbook_Open () every time you open the workbook.

Recording an Auto_Open Macro

You can record an Auto_Run macro as follows −

  • Click the VIEW tab on the Ribbon.
  • Click Macros.
  • Click Record Macro. The Record Macro dialog box appears.
  • Type Auto_Run for the macro name.
  • Type a description and click OK.
Auto_open
  • Start recording the macro.
  • Stop Recording.
  • Save the workbook as macro enabled workbook.
  • Close the workbook.
  • Open the workbook. The macro Auto_Run will run automatically.

If you want Excel to start without running an Auto_Open macro, hold the SHIFT key when you start Excel.

Limitations of Auto_Open Macro

The following are the limitations of Auto_Open macro −

  • If the workbook in which you saved the Auto_Open macro contains code for workbook Open event, the code for the Open event will override the actions in the Auto_Open macro.

  • An Auto_Open macro is ignored when the workbook is opened by running code that uses the Open method.

  • An Auto_Open macro runs before any other workbooks open. Hence, if you record actions that you want Excel to perform on the default Book1 workbook or on a workbook that is loaded from the XLStart folder, the Auto_Open macro will fail when you restart Excel, because the macro runs before the default and startup workbooks open.

If you encounter any of these limitations, instead of recording an Auto_Open macro, you must write a code for the Open event as described in the next section.

VBA Code for Open Event of a Workbook

You can write code that will get executed when you open a workbook. VBA provides you with an event called open that incorporates a VBA procedure for the actions to be done on opening a workbook.

Open the workbook in which you stored the macro that you have written for the absolute references – Report_ProjectXYZ. When this macro is run, a new worksheet will be added in the workbook and the project report structure appears on the new worksheet.

You can write a macro code that will perform these actions when you open the workbook. That means when you open the Project Report workbook, a new worksheet with the report structure will be ready for you to enter the details.

Follow the below given procedure in VBA editor−

  • Double click on ThisWorkbook in Projects Explorer.

  • In the code window, select Workbook in the left dropdown list and Open in the right dropdown list. Sub Workbook_Open () appears.

Workbook_open

  • Click Modules in the Projects Explorer.

  • Double click on the module name that contains the macro code.

  • Copy the macro code from the module and paste it in the Sub WorkBook_Open ().

Sub Workbook_open

Save the macro-enabled workbook. Open it again. The macro runs and a new worksheet with the report structure is inserted.

Advertisements