Pin Me

Changing Macro Settings in Excel 2007

written by: •edited by: Rebecca Scudder•updated: 1/9/2010

Having trouble using an Excel shared workbook or add-in because of macro security settings? Read on to find out how to modify these settings in Excel 2007.

  • slide 1 of 1

    Excel Macro Settings

    If you’ve recently received a shared workbook containing macros in Excel, you may be having trouble using it if your macro settings haven’t been adjusted. To make changes to these settings, follow these steps.

    How to Change the Macro Security Settings

    Step 1: Click on the Office button in the upper left-hand corner of the Excel interface, and then click on Excel Options. (Click any image for a larger view.)

    Select Excel Options 

    Step 2: In the Excel Options window, select Trust Center from the list of categories in the left panel. Then, click on Trust Center Settings.

    Select Trust Center Settings 

    Step 3: Once in the Trust Center window, select Macro Settings from the list in the left panel of the window.

    Trust Center Macro Settings 

    Here, you’ll be able to see the four different macro security settings:

    • Disable all macros without notification – If you select this option, all macros that aren’t in a designated trusted location are disabled. Additionally, you will not receive any alert messages when a workbook you are using contains these macros – they simply will not work.
    • Disable all macros with notification – This option also disables all macros that aren’t in a trusted location. However, an alert will be displayed notifying you when such macros are present so that you can enable these tools if you need them.
    • Disable all macros except digitally signed macros – This option is the same as the one above, except that digitally signed macros created by people you have designated as trusted publishers will still be permitted to run.
    • Enable all macros – If you select this option, Excel will allow any macro to run. This is, by far, the least secure macro setting, and you should only choose this option if you are extremely confident about the contents of all Excel files on your computer.

    Step 4: Select the setting that you want to apply and then click OK to exit the Trust Center. Click OK again to exit the Excel Options window.

    Note: When trying to get a macro to run in a shared workbook or add-in, many people will get frustrated and just choose the Enable all macros option. (I know, I’ve been there myself when in a hurry.) Even though this seems like a quick way to access the information you need, it’s much better to take a few extra moments and add that workbook’s directory to your list of Trusted Locations instead. You can perform this function in the Trust Center as well.

    To do this, return to the Trust Center and select Trusted Locations from the left panel. Click on the Add new location button and browse for the directory containing your workbook or add-in.

    Trusted Locations 

    Looking for more Excel tips and tricks? Be sure to browse through the ever-expanding library of Microsoft Excel tutorials and user guides available on Bright Hub’s Windows Channel. Learn how to create various charts and graphs, find out how to record your own custom macros, discover tips on how to use Excel’s data validation tools, and more. Additional articles are being added on a regular basis, so check back often!