One advantage of writing Excel macros in VBA is that you can have
Excel 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 still have that formatting applied 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 manually, or even from a toolbar button
or a menu, you can have Excel run a macro whenever a workbook is opened.
The trick of making that happen is in the name you give the macro.
Whenever Excel finds a macro with the name Auto_Open, it runs the macro
when the workbook to which it is attached is opened.
Tip
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, and then
clicking OK allows the Auto_Open macro to run.
In this exercise, you’ll create and test a macro that runs
whenever someone opens the workbook to which it is attached.
Set Up
Open the
RunOnOpen_start workbook, click the Enable Content button on the
Message Bar (if necessary), and save the workbook as
RunOnOpen. Then follow the steps.
-
On the View tab, in the
Macros group, click the Macros arrow and then, in the list that
appears, click Record
Macro.
The Record Macro dialog box opens.
-
In the Macro name box,
delete the existing name, and then type Auto_Open.
-
Click OK.
The Record Macro dialog box closes.
-
Select the cell range B3:C11.
-
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.
-
Click cell C11 and then, in
the Macros list, click Stop Recording.
Excel stops recording your macro.
-
In the Macros list, click
View Macros.
The Macro dialog box opens.
-
Click Highlight, and then
click Run.
The contents of cells C4, C6, and C10 appear in bold
type.
-
On the Quick Access Toolbar, click the Save button to save your work.
-
Click the Close button to
close the RunOnOpen
workbook.
-
Click the File tab and
then, in the Recent Documents
list, click RunOnOpen.xlsm. If a
warning appears, click Enable
Content, and then click OK to enable macros. RunOnOpen opens, and
the contents of cells C4, C6, and C10 change immediately to regular
type.
-
On the Quick Access Toolbar, click the Save button to save your work.
