Run a macro

ShowRun a macro

When the macro security level in Excel is set to High, Excel will run only those macros that are digitally signed or stored in a trusted location, such as the Excel startup folder.

  1. If the macro you want to run is not digitally signed or located in a trusted location, you can temporarily set the security level to Medium or Low.

ShowHow?

  1. On the Tools menu, click Options.
  2. Click the Security tab.
  3. Under Macro Security, click Macro Security.
  4. Click the Security Level tab, and then select the security level you want to use.
  1. Open the workbook that contains the macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.).
  2. On the Tools menu, point to Macro, and then click Macros.
  3. In the Macro name box, enter the name of the macro you want to run.
  4. Do one of the following:

Run a macro in a Microsoft Excel workbook

  • Click Run.

If you want to interrupt the execution of the macro, press ESC.

Run a macro from a Microsoft Visual Basic module

  1. Click Edit.
  2. Click Run Sub/UserForm Button image.

Tip    If you want to run a different macro while you are in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.), click Macros on the Tools menu. In the Macro name box, enter the name of the macro you want to run, and then click Run.

ShowStart a macro from a keyboard shortcut

  1. On the Tools menu, point to Macro, and then click Macros.
  2. In the Macro name box, enter the name of the macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) you want to assign to a keyboard shortcut key (shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.).
  3. Click Options.
  4. If you want to run the macro by pressing a keyboard shortcut key (shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.), enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key cannot use a number or special character, such as @ or #.

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

  1. If you want to include a description of the macro, type it in the Description box.
  2. Click OK.
  3. Click Cancel.

ShowStart a macro from a toolbar button

  1. Create a button for the macro.

ShowHow?

  1. Make sure the toolbar to which you want to add a new button is displayed.

If needed, point to Toolbars on the View menu, and then click the toolbar you want.

  1. On the Tools menu, click Customize, and then click the Commands tab.
  2. In the Categories box, click Macros.
  3. Drag the command or macro you want from the Commands box to the displayed toolbar.
  1. Click the new button to display the Assign Macros dialog box.
  2. Do one of the following:

 Note   If you assign a macro to a button or other object that is already in use as a hyperlink (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an intranet. Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.), the hyperlink information is deleted. From then on, clicking the button or object runs the macro instead.

ShowStart a macro from an area, or hotspot, on a graphic object

  1. Select an existing object.
  2. Draw or insert another object to create a hotspot on the existing object.
  3. Select the hotspot you created, and then right-click a sizing handle (sizing handle: One of the small circles or squares that appears at the corners and sides of a selected object. You drag these handles to change the size of the object.) to display the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.).
  4. On the shortcut menu, click Assign Macro.
  5. Do one of the following:
  6. Click OK, and then select the hotspot.
  7. On the Format menu, click AutoShape, and then click the Color and Lines tab.
  8. Under Fill, click No Fill in the Color box.
  9. Under Line, click No Line in the Color box.
  10. Repeat these steps for each hotspot you create. You do not need to create a new graphic object for each hotspot.

ShowHave a macro run automatically when you open a workbook

If you record a macro and save it 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 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 you open the workbook.

ShowCreate an Auto_Open macro

  1. If you want to save the macro with a particular workbook, open that workbook first.
  2. On the Tools menu, point to Macro, and then click Record New Macro.
  3. In the Macro name box, type Auto_Open.
  4. In the Store macro in box, specify where to save the macro:
    • To save the macro in the current workbook, choose This Workbook.
    • To create a new workbook to save the macro in, choose New Workbook.
    • To save the macro in a hidden workbook that is automatically loaded when you start Excel, choose Personal Macro Workbook.

 Note    If you choose Personal Macro Workbook, Excel will create and save the macro in a hidden workbook named Personal.xls (if Personal.xls doesn't already exist). In Windows 2000, Personal.xls is saved in the C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLStart folder so that it will be loaded automatically whenever Excel starts. If you want the macro that you record and save in Personal.xls to act on a particular workbook, you must also save that workbook in the XLStart folder so that it will also be opened when Excel starts.

  1. Click OK, and then perform the actions that you want to record.
  2. On the Stop Recording toolbar, click the Stop Recording button.
  3. If you chose to save the macro in This Workbook or New Workbook in step 4, make sure to save or move the workbook into one of the XLStart folders.

Recording an Auto_Open macro has a number of limitations:

  • Many actions 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 loaded from the XLStart folder, the Auto_Open macro will fail when you restart Excel because it runs before the default and start-up 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.

ShowCreate 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. Save and close all open workbooks.
  2. Open the workbook you want add the macro to, or create a new workbook.
  3. On the Tools menu, point to Macro and then click Visual Basic Editor.
  4. In the Project Explorer window, right-click the ThisWorkbook object and then click View Code on the shortcut menu.
  5. In the Object list above the Code window, select Workbook. This will automatically create an empty procedure for the Open event like this:
Private Sub Workbook_Open()

End Sub
  1. Add the following lines of code to the procedure:
Private Sub Workbook_Open()
    MsgBox Date
    Worksheets("Sheet1").Range("A1").Value = Date
End Sub
  1. Switch to Microsoft Excel and save the workbook.
  2. 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.
  3. Click OK in the message box.
  4. Note that cell A1 on Sheet1 also contains the date as a result of running the Workbook_Open procedure.
 
 
Applies to:
Excel 2003