Run a macro

There are several ways to run a macro . You can always run a macro by using the menu command. Depending on how a macro is assigned to run, you may also be able to run it by pressing a CTRL combination shortcut key or clicking a toolbar button or an area on an object, graphic, or control. Also, you can run a macro automatically when the workbook is opened.

Note When the macro security level in Microsoft Office Excel is set to Disable all macros without notification, Excel will run only those macros that are digitally signed or stored in a trusted location, such as the Excel startup folder. If the macro that you want to run is not digitally signed or located in a trusted location, you can temporarily change the security level that enables all macros.What do you want to do?

Run a macro

Run a macro by pressing a CTRL combination shortcut key

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

Run a macro by clicking an area on a graphic object

Run a macro automatically upon opening a workbook

Run a macro

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon 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.

      

   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 of the settings that disable all macros after you finish working with macros.

3. Open the workbook that contains the macro.
4. On the Developer tab, in the Code group, click Macros.
5. In the Macro name box, click the macro that you want to run.
6. 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 module, click Edit, and then on the Run menu, click Run Sub/UserForm , or press F5.

   Tip You can run a different macro while you are working in the Visual Basic Editor. On the Developer tab, in the Code group, click Macros. In the Macro name box, click the macro that you want to run, and then click Run.

Run a macro by pressing a CTRL combination shortcut key

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

   

2. In the Macro name box, click the macro that you want to assign to a CTRL combination shortcut key.
3. Click Options.
4. In the Shortcut key box, type any lowercase letter or uppercase letter that you want to use.

   Note The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For a list of CTRL combination shortcut keys that are already assigned in Excel, see Excel shortcut and function keys.

5. To include a description of the macro, in the Description box, type the text that you want.
6. Click OK, and then click Cancel.

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

1. Click the Microsoft Office Button , and then click Excel Options.
2. Click Customize, and then in the Choose commands from list, select Popular Commands.
3. In the list box, click View Macros, and then click Add.
4. Click OK.

   Tip The View Macros button will be added to the Quick Access Toolbar.

   

5. On the Quick Access Toolbar, click the View Macros button.
6. In the Macro name box, click the macro that you want to run, and then click Run.

Run a macro by clicking an area on a graphic object

1. In the worksheet, select an existing 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.

   

3. Right-click the hot spot that you created, and then click Assign Macro on the shortcut menu .
4. Do one of the following:
   • To assign an existing macro to the graphic object, double-click the macro or enter its name in the Macro name box.
   • To record a new macro to assign to the selected graphic object, click Record. When you finish recording the macro, click Stop Recording on the Developer tab in the Code group.

     Tip You can also click Stop Recording on the left side of the status bar.

   • To edit an existing macro, click the name of the macro in the Macro name box, and then click Edit.
5. Click OK.
6. In the worksheet, select the hot spot.

   Tip This displays the Drawing Tools, adding a Format tab.

   

7. On the Format tab, in the Shape Styles group, do the following:
   • Click the arrow next to Shape Fill, and then click No Fill.
   • Click the arrow next to Shape Outline, and then click No Outline.

Run a macro automatically upon opening a workbook

If you record a macro and save it by using 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 Visual Basic for Applications (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 each time that 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 Microsoft Office Button , and then click Excel Options.
   2. In the Popular category, under Working with Excel and other Office applications, select the Developer tools 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.

      

   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 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 in which 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 choose Personal Macro Workbook, Excel creates a hidden personal macro workbook (Personal.xlsb), if it doesn't already exist, and saves the macro in this workbook. In Microsoft Windows XP, this workbook is saved in the C:\Documents and Settings\user name\Application Data\Microsoft\Excel\XLStart folder so that it can be loaded automatically whenever Excel starts. In Microsoft Windows Vista, this workbook is saved in the C:\Users\user name\Application Data\Microsoft\Excel\XLStart folder. 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.

7. Click OK, and then perform the actions that you want to record.
8. On the Developer tab, in the Code group, click Stop Recording .

   Tip You can also click Stop Recording on the left side of the status bar.

   Notes:

   • If you chose to save the macro in This Workbook or New Workbook in step 6, save or move the workbook into one of the XLStart folders.
   • Recording an Auto_Open macro has the following limitations:
   • • Many actions that you may want to perform cannot be recorded.
     • If the workbook in which 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 are opened. 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 are opened.

     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.

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 Microsoft Office Button , and then click Excel Options.
   2. In the Popular category, under Working with Excel and other Office applications, select the Developer tools 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.

      

   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 of the settings that disable all macros after you finish working with macros.

3. Save and close all open workbooks.
4. Open the workbook that you want to add the macro to, 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 on the shortcut menu.

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

7. In the Object list above the Code window, select Workbook.

   This automatically creates an empty procedure for the Open event like 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.
12. Note that cell A1 on Sheet1 also contains the date as a result of running the Workbook_Open procedure.

See also:

• Enable or disable macros in Office documents
• Overview of macro security