Excel Macros Disabled? – How to Enable Macros

Macros are disabled by default in Microsoft Excel and other MS Office applications, and need to be explicitly enabled by users.  This article will cover the various methods that Excel provides to enable macros and manage your security settings, allowing you to run your own macros safely.

Warning!

First, it’s important to understand just how dangerous macros can be.

Macros are actually little computer programs, stored in workbooks as VBA code.  While mostly used to automate tasks inside Excel, VBA programmers can write powerful macros that can do almost anything with your computer and access any resources it’s connected to.

This power is meant extend the functionality of Excel, and it does – but in the wrong hands it can be twisted to spread ransomware, hijack computers for botnets, steal data from databases, send e-mail spam, and otherwise cause havoc on computers and their networks.

With this in mind, you should always be wary of macro-enabled workbooks that come from other people, and only enable macros for workbooks that you trust.

Enable Macros Temporarily – for Individual Workbooks

By default, when you first open a macro-enabled workbook you’ll see a yellow “SECURITY WARNING” bar appear just underneath the ribbon.  Clicking the “Enable Content” button will enable macros.

NOTE: this will trigger any macros that run when the workbook is opened, so don’t click this by mistake!

If you don’t want to enable macros, you can click the ‘X’ on the far-right of the yellow bar.  The security warning will disappear, but any attempt to run a macro will fail with a warning message.  (Opening the VBA Editor or attempting to run a macro before dealing with the security warning will also clear the bar and disable macros.)

If you’ve disabled macros accidentally, you’ll have to close and re-open the workbook, then click the “Enable Content” button.

Macro Settings in the Trust Center

Excel provides settings to adjust its default behavior when opening macro-enabled workbooks.  You can view these settings in the Trust Center:

• Select File > Options, then select Trust Center in the left-hand list and click “Trust Center Settings…”

• In the Trust Center dialog, select “Macro Settings”

Disable all macros without notification: you won’t be able to enable or run macros when you open a workbook.  You’ll still be able to make and run macros in new workbooks, though.

Disable all macros with notification: this is the default option, allowing macros to be enabled from the Security Warning bar when you open a workbook.

Disable all macros except digitally signed macros: you won’t see any warnings, but only digitally signed macros will be able to run.  Such macros are made by VBA developers, using certificates provided by a commercial authority or a security administrator in your organization.

Enable all macros (not recommended; potentially dangerous code can run): all macros are enabled without warning.

Trust access to the VBA project object model: this setting allows other programs (and macros) to modify macros in any open workbooks.  Some external analytics programs will require you to enable this to work, but usually you should leave this unchecked.

Enable Macros Permanently – for Individual Workbooks

You can set a macro-enabled workbook to be a Trusted Document so that when you re-open it, macros will be enabled with no security warnings.  Trusted workbooks are added to a private list associated with your Windows login.

To trust a workbook:

• Open the workbook, then click File while the Security Warning bar is still visible
• Click “Enable Content”, then select “Enable All Content” in the dropdown

NOTE: Excel doesn’t provide any way to un-trust a particular workbook, but it does allow you to un-trust all previously trusted workbooks.  To do this:

• Select File > Options, then select Trust Center in the left-hand list and click “Trust Center Settings…”
• In the Trust Center dialog, select “Trusted Documents”

• If you want to clear the list of trusted workbooks, click “Clear”
• If you want to disable Trusted Documents, check “Disable Trusted Documents”; you’ll still be able to enable macros temporarily when a workbook is opened

Due to the limited functionality Excel provides for trusting individual documents, a better solution is to use Trusted Locations (see below).

Enable Macros Permanently – for All Workbooks in a Trusted Location

Rather than trusting individual workbooks, you can set Excel to trust certain locations on your computer or network.  Any workbooks in a Trusted Location will open with macros enabled and no security warnings.

View Trusted Locations

Unlike Trusted Documents, a list of Trusted Locations is available for you to view at any time.

• Select File > Options, then select Trust Center in the left-hand list and click “Trust Center Settings…”
• In the Trust Center dialog, select “Trusted Locations” in the left-hand list

 

By default, you’ll see several trusted locations already set by Excel.  These locations are used by Excel to enable macros in new workbooks and add-ins, and should not be modified.

Add a new Trusted Location

Technically, you can put your own workbooks in the default Trusted Locations, but it’s better to define your own if you want to take advantage of this feature.

• Click “Add new location…” to show the “Microsoft Office Trusted Location” dialog

• Enter the path to the folder you want to use in the top textbox (or click “Browse…” to navigate to the folder)
• If you want all subfolders of the selected folder to be trusted as well, check “Subfolders of this location are also trusted”
• (Optional) Enter a description of this trusted location. Useful if you need to manage several locations