Run an Excel macro

Excel provides several ways to run a macro. A macro is an action or a set of actions that you can use to automate tasks. To run a macro, you can:

  • Go to the Developer tab, and in the Code group, click Macros

  • Press Ctrl plus a shortcut key, depending on how the macro is set to run.

  • Click a button on the Quick Access Toolbar or in a custom group on the ribbon.

  • Click an area on an object, graphic, or control.

  • Run a macro automatically when you open a workbook.

What do you want to do?

Run an Excel macro

Run an Excel macro by pressing Ctrl plus a shortcut key

Run an Excel macro by clicking a button on the Quick Access Toolbar

Run an Excel macro by clicking a button in a custom group on the ribbon

Run an Excel macro by clicking an area on a graphic object

Configure an Excel macro to run when you open a workbook

Run an Excel macro

Before you run macros

You may first need to change a few settings in Excel before you can run macros:

  1. If the Developer tab is not available, do the following to display it:

    1. Click the File tab, click Options, and then click the Customize Ribbon category.

    2. In the Main Tabs list, click the Developer check box, and then click OK.

  2. To set the security level temporarily to enable all macros, do the following:

    1. On the Developer tab, in the Code group, click Macro Security.

      Code group on the Developer tab in Excel

    2. In the Macro Settings category, under Macro Settings, click Enable all macros (not recommended; potentially dangerous code can run), and then click OK.

      Note    To help prevent potentially dangerous code from running, return to any one of the settings that disable all macros after you finish working with macros.

Run the macro

  1. Open the workbook that contains the macro.

  2. On the Developer tab, in the Code group, click Macros.

  3. In the Macro name box, click the macro that you want to run.

    Code group on the Developer tab in Excel

  4. Do one of the following:

    • To run a macro in an Excel workbook, click Run.
      Tip    You can also press Ctrl+F8 to run the macro. You can interrupt the execution of the macro by pressing Esc.

    • To run a macro from a Microsoft Visual Basic for Applications (VBA) module, click Edit, and then on the Run menu, click Run Sub/UserForm , or press F5.

Top of Page

Run an Excel macro by pressing Ctrl plus a shortcut key

  1. If the Developer tab is not available, do the following to display it:

    1. Click the File tab, click Options, and then click the Customize Ribbon category.

    2. In the Main Tabs list, select the Developer check box, and then click OK.

  2. On the Developer tab, in the Code group, click Macros.

  3. In the Macro name box, click the macro that you want to assign to a Ctrl combination shortcut key.

  4. Click Options. The Macro Options dialog box appears.

  5. In the Shortcut key box, type any lowercase letter or uppercase letter that you want to use with the Ctrl key.

    Note    The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open.

  6. In the Description box, type a description of the macro.

  7. Click OK to save your changes, and then click Cancel to close the Macro dialog box.

Top of Page

Run an Excel macro by clicking a button on the Quick Access Toolbar

To run a macro from a button on the Quick Access toolbar, you first have to add the button to the toolbar. To do that:

  1. Click the File tab, click Options, and then click Quick Access Toolbar.

  2. In the Choose commands from list, select Macros.

  3. In the list, click the macro that you created, and then click Add.

  4. To change the button image of the macro, select the macro in the box to which it was added, and then click Modify.

  5. Under Symbol, click the button image that you want to use.

  6. To change the name of the macro that is displayed when you rest the pointer on the button, in the Display name box, type the name that you want to use.

  7. Click OK to add the macro button to the Quick Access Toolbar.

  8. On the Quick Access Toolbar, click the macro button that you just added.

Top of Page

Run an Excel macro by clicking a button in a custom group on the ribbon

By taking advantage of the customizability of the ribbon, you can create a custom group that appears on a tab in the ribbon, and then assign a macro to a button in that group. For example, you can add a custom group named "My Macros" to the Developer tab, and then add a macro to the new group that appears as a button.

Top of Page

Run an Excel macro by clicking an area on a graphic object

You can create a hot spot on a graphic that users can click to run a macro.

  1. In the worksheet, insert a graphic object, such as a picture, clip art, shape, or SmartArt.

  2. To create a hot spot on the existing object, on the Insert tab, in the Illustrations group, click Shapes, select the shape that you want to use, and then draw that shape on the existing object.

    Illustrations group on the Insert tab in Excel

  3. Right-click the hot spot that you created, and then click Assign Macro.

  4. Select the macro you want to use and click OK.

  5. Right-click the shape again and click Format Shape.

    The Format Shape task pane appears.

  6. Under Fill, click No Fill, and under Line, select No Line.

Top of Page

Configure an Excel macro to run when you open a workbook

If you record a macro and save it with the name "Auto_Open," the macro will run whenever you open the workbook that contains the macro. Another way to automatically run a macro when you open a workbook is to write a VBA procedure in the Open event of the workbook by using the Visual Basic Editor. The Open event is a built-in workbook event that runs its macro code every time you open the workbook.

Create an Auto_Open macro

  1. If the Developer tab is not available, do the following to display it:

    1. Click the File tab, and then click Options.

    2. In the Customize Ribbon category, in the Main Tabs list, select the Developer check box, and then click OK.

  2. If you get a warning message about macros, you'll need to set the security level temporarily to enable all macros.

    1. On the Developer tab, in the Code group, click Macro Security.
      Code group on the Developer tab in Excel

    2. In the Macro Settings category, under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.
      Note
          To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

  3. If you want to save the macro with a particular workbook, open that workbook first.

  4. On the Developer tab, in the Code group, click Record Macro.

  5. In the Macro name box, type Auto_Open.

  6. In the Store macro in list, select the workbook where you want to store the macro.
    Tip    If you want a macro to be available whenever you use Excel, select Personal Macro Workbook. When you select that option, Excel creates a hidden personal macro workbook (Personal.xlsb), if it does not already exist, and saves the macro there.

In Windows 7, Personal.xlsb is saved in C:\Users\user name\AppData\Roaming\Microsoft\Excel\XLStart. In Windows Vista, this workbook is saved in the C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart folder. If you can't find it there, it may have been saved in the Roaming subfolder instead of Local. Workbooks in the XLStart folder are opened automatically whenever Excel starts.

If you want a macro in the personal macro workbook to be run automatically in another workbook, you must also save that workbook in the XLStart folder so that both workbooks are opened when Excel starts.

  1. Click OK, and then perform the actions that you want to record.

  2. After you've recorded all your actions, on the Developer tab, in the Code group, click Stop Recording Button image .
    Tip    You can also click Stop Recording on the left side of the status bar.

    Stop Recording button on lower-left side of Excel worksheet

Notes

  • If you chose to store the macro in This Workbook or New Workbook in step 6, save or move the workbook into the XLStart folder on your computer.

  • Recording an Auto_Open macro has the following limitations:

    • If the workbook where you save the Auto_Open macro already contains a VBA procedure in its Open event, the VBA procedure for the Open event will override all actions in the Auto_Open macro.

    • An Auto_Open macro is ignored when a workbook is opened programmatically by using the Open method.

    • An Auto_Open macro runs before any other workbooks open. Therefore, 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 these limitations, instead of recording an Auto_Open macro, you must create a VBA procedure for the Open event as described in the next section of this article.

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

Create a VBA procedure for the Open event of a workbook

The following example uses the Open event to run a macro when you open the workbook.

  1. If the Developer tab is not available, do the following to display it:

    1. Click the File tab, and then click Options.

    2. In the Customize Ribbon category, in the Main Tabs list, select the Developer check box, and then click OK.

  2. If you get a warning message about macros, you'll need to set the security level temporarily to enable all macros.

    1. On the Developer tab, in the Code group, click Macro Security.
      . Code group on the Developer tab in Excel

    2. In the Macro Settings category, under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.
      Note
          To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

  3. Save and close all open workbooks.

  4. Open the workbook where you want to add the macro, or create a new workbook.

  5. On the Developer tab, in the Code group, click Visual Basic.

  6. In the Project Explorer window, right-click the ThisWorkbook object, and then click View Code.
    Project Explorer window showing code for this Excel workbook

    Tip    If the Project Explorer window is not visible, on the View menu, click Project Explorer.

  7. From the Object list above the Code window (the word General), select Workbook. This automatically creates an empty procedure for the Open event, such as this:

    Private Sub Workbook_Open()

    End Sub

  8. Add the following lines of code to the procedure:

    Private Sub Workbook_Open()

    MsgBox Date
    Worksheets("Sheet1").Range("A1").Value = Date

    End Sub

  9. Switch to Excel and save the workbook as a macro-enabled workbook (.xlsm).

  10. Close and reopen the workbook. When you open the file again, Excel runs the Workbook_Open procedure, which displays today's date in a message box.

  11. Click OK in the message box.

    Note that cell A1 on Sheet1 also contains the date as a result of running the Workbook_Open procedure.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×