Running Macros When a Workbook Is Opened

One advantage of writing Excel 2007 macros in VBA is that you can have Excel 2007 run a macro whenever a workbook is opened. For example, if you use a worksheet for presentations, you can create macros that render the contents of selected cells in bold type, italic, or different typefaces to set the data apart from data in neighboring cells. If you close a workbook without removing that formatting, however, the contents of your workbook will have highlights when you open it. Although this is not a catastrophe, returning the workbook to its original formatting might take a few seconds to accomplish.

Instead of running a macro by hand, or even from a toolbar button or a menu, you can have Excel 2007 run a macro whenever a workbook is opened. The trick of making that happen is in the name you give the macro. Whenever Excel 2007 finds a macro with the name Auto_Open, it runs the macro when the workbook to which it is attached is opened.

Note

If you have your macro security set to the Disable with Notification level, clicking the Options button that appears on the Message Bar, selecting the Enable this Content option button, and then clicking OK allows the Auto_Open macro to run.

In this exercise, you will create a macro to run whenever someone opens the workbook to which it is attached.

USE the RunOnOpen workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Macros folder.

OPEN the RunOnOpen workbook.

1.	If necessary, click the Options button that appears on the Message Bar. The Microsoft Office Security Options dialog box appears.
2.	Select the Enable this content option button and then click OK. The Microsoft Office Security Options dialog box disappears.
3.	On the View tab, in the Macros group, click the Macros button down arrow and then click Record Macro. The Record Macro dialog box appears.
4.	In the Macro name box, delete the existing name and then type Auto_Open.
5.	Click OK. The Record Macro dialog box disappears.
6.	Select the cell range B3:C11.
7.	On the Home tab, in the Font group, click the Bold button twice. The first click of the Bold button formats all the selected cells in bold; the second click removes the bold formatting from all the selected cells.
8.	On the View tab, in the Macros group, click the Macros button down arrow and click Stop Recording. Excel 2007 stops recording your macro.
9.	On the View tab, in the Macros group, click the Macros button down arrow and then click View Macros. The Macro dialog box appears.
10.	Click Highlight and then click Run. The contents of cells C4, C6, and C10 appear in bold type.
11.	On the Quick Access Toolbar, click the Save button to save your work.
12.	Click the Close button to close RunOnOpen.xls.
13.	Click the Microsoft Office button and then click RunOnOpen.xlsx. If a warning appears, click Options, select the Enable this content option button, and then click OK to enable macros. RunOnOpen.xls opens, with the contents of cells C4, C6, and C10 changing immediately to regular type.
14.	On the Quick Access Toolbar, click the Save button to save your work.

CLOSE the RunOnOpen workbook.

CLOSE Excel.