Running the Macro

Macros can be run in several different ways. They can be launched from the Macro dialog box, by using an assigned shortcut key, from a button on the Quick Access Toolbar, or from within another macro. We will explore each of these approaches except the shortcut key in this workshop.

Using the Macro Dialog Box

Let's first see how to run the macro we created from the Macro dialog box.

Step1. To select the next sheet,

Click the 2004 worksheet tab

The 2004 worksheet appears.

Step2. To access the macro commands, on the right side of the Ribbon,

Click

NOTE for MacOS Users: To access the macro commands, on the Ribbon in the View tab, Click.

The Macro dialog box appears.

Step3. To run the AutoFit macro, in the Macro dialog box,

Click AutoFit, Click

We see that the columns all fit to their data, just like what we recorded.

Let's now add our macro to the Quick Access Toolbar. The steps for this section vary between Windows and MacOS.

NOTE for MacOS Users: Skip foward to the heading, "Adding a Macro to the Quick Access Toolbar on MacOS".

Adding a Macro to the Quick Access Toolbar in Windows

If a macro is to be used often, adding it to the Quick Access Toolbar will make access easy.

NOTE: If you add a macro to the Quick Access Toolbar that is stored within an individual workbook (not the Personal Macro Workbook), it will only be available when that workbook is open.

Step1. To begin customizing the Quick Access Toolbar,

Click File, Click

You see the Excel Options dialog box.

Step2. To customize the Quick Access Toolbar, in the navigation pane,

Click Quick Access Toolbar

You see the Quick Access Toolbar options of the Excel Options dialog box:

The left column contains all the commands that can be added to the Quick Access Toolbar, and the right column lists the commands already on the toolbar.

We will need to find our macros in the left column and place them in the right column.

Step3. To find macros in the choose commands from drop-down list on the left,

Click, Click Macros

We see a list of all our macros.

We would like to separate the macros section of our Quick Access Toolbar from the standard office commands for the sake of organization. This can be accomplished by inserting a <Separator> at the beginning of the macro toolbar items.

Step4. To insert the <Separator>, in the Choose command from: column,

Double-Click <Separator>

We see <Separator> in the right column. We will now add our macro to the Quick Access Toolbar as well.

Step5. To add AutoFit to the Quick Access Toolbar, in the left column,

Double-Click AutoFit

AutoFit is now added to the Quick Access Toolbar. Let's explore modifying it further.

Changing the Icon and Name of a Button

Once a command is added to the Quick Access Toolbar, we can modify the way it looks and the caption it displays when the mouse hovers over it.

Step1. To begin modifying the AutoFit button, in the right column,

Click AutoFit

Now that the macro is selected, we can start changing its name and icon.

Step2. To open the modify menu, below the right column,

Click

You see the Modify Button dialog box:

Step3. To change the display icon,

Click any icon

We also want to change the name that appears in the button's tooltip.

Step4. To select the current display name, in the Display Name field,

Press & Drag the current value, type: Auto Fit

Let's accept our changes.

Step5. To make the change,

Click

We are returned to the Customize window.

Step6. To close the Customize window,

Click

We now see a separator and a button with the icon we created.

NOTE: To remove a command button from the Quick Access Toolbar, Right-Click the button and select Remove from Quick Access Toolbar.

Windows Notes: Skip foward to the heading, "Using the Quick Access Toolbar to Run a Macro".

Adding a Macro to the Quick Access Toolbar on MacOS

If a macro is to be used often, adding it to the Quick Access Toolbar will make access easy.

Step1. To begin customizing the Quick Access Toolbar, in the Menu bar,

Click Excel, Click Preferences...

You see the Preferences dialog box.

We need to change the Ribbon & Toolbar preferences.

Step2. To customize the Quick Access Toolbar,

Click

You see the Ribbon & Toolbar dialog box:

3. To customize the Quick Access Toolbar, find macros in the choose commands from drop-down list on the left,

Click, Click Macros

We see a list of all our macros.

Step4. To add AutoFit to the Quick Access Toolbar, in the left column,

Click AutoFit, Click, Click

AutoFit is now added to the Quick Access Toolbar. Let's explore modifying it further.

Changing the Name of a Button on MacOS

While we can't change the icon associated with a macro, we can change its name on MacOS.

Step1. To rename the macro on the Quick Access Toolbar, in the right pane of the Ribbon & Toolbar dialog box,

Click <<No Label>>, Click, Click Rename...

A drop-down menu opens.

Step2. To change the name of the button, type:

Auto Fit, Click

The tool has been renamed.

Step3. Close the Preferences window.

The Auto Fit macro has been added to the Quick Access Toolbar:

Let's use the button to run the AutoFit macro.

Using the Quick Access Toolbar to Run a Macro

We can use the new Auto Fit button to format a sheet in one simple step. Let's use the button to format the cells on the 2005 worksheet.

Step1. To select the 2005 worksheet,

Click the 2005 worksheet tab

The 2005 worksheet appears.

Step2. To run the macro, on the Quick Access Toolbar,

Click the Auto Fit button

The columns are widened with just a single click of the mouse. Let's save our work.

It would be nice if this macro was run when this workbook is opened. Let's see how this is done.