How to enable and disable macros in Excel

The article looks at how to turn on macros in Excel, explains the basics of macro security and shows how to adjust the security settings to run VBA codes safely.

Like almost any technology, macros can be used for both good and evil. Therefore, in Microsoft Excel, all macros are disabled by default. This tutorial covers different ways to enable macros in Excel and explains potential risks associated with that.

Macro security in Excel

Before you go enable macros in your worksheets, it's important to understand how dangerous they can possibly be.

Though VBA codes are very effective in automating complex and repetitious tasks, they are a significant source of risk from the security point of view. A malicious macro that you run unwittingly may damage or completely delete files on your hard drive, mess up your data, and even corrupt your Microsoft Office installation. For this reason, Excel's default setting is to disable all macros with notification.

How to avoid these dangers? Just follow one simple rule: enable only safe macros – ones that you've written or recorded yourself, macros from trusted sources, and VBA codes that you have reviewed and fully understand.

How to enable macros for individual workbooks

There are two ways to turn on macros for a certain file: directly from the workbook and through the Backstage view.

Enable macros via security warning bar

With the default macro settings, when you first open a workbook containing macros, the yellow security warning bar appears at the top of the sheet right under the ribbon:
Enabling macros via security warning bar

If the Visual Basic Editor is open at the time you are opening the file with macros, the Microsoft Excel Security Notice will be displayed:
Microsoft Excel Security Notice

If you trust the source of the file and know that all the macros are secure, click the Enable Content or Enable Macros button. This will turn on the macros and make the file a trusted document. The next time you open the workbook, the security warning won't appear.

If the source of the file is unknown and you don't want to enable macros, you can click the 'X' button to close the security warning. The warning will disappear, but macros will remain disabled. Any attempt to run a macro will result in the following message.
A warning that macros have been disabled.

If you've disabled macros accidentally, simply re-open the workbook, and then click the Enable Content button on the warning bar.

Turn on macros in Backstage view

Another way to enable macros for a specific workbook is via the Office Backstage view. Here's how:

  1. Click the File tab, and then click Info in the left menu.
  2. In the Security Warning area, click Enable Content > Enable All Content.

As with the previous method, your workbook will become a trusted document.
Turn on macros via the Backstage view

What you should know about trusted documents in Excel

Enabling macros through either a message bar or Backstage view makes the file a trusted document. However, some Excel files cannot be made trusted documents. For examples, files opened from an unsafe location such as the Temp Folder, or if the system administrator has set the security policy in your organization to disable all macros without notification. In such cases, the macros are only enabled for a single time. On the next opening of the file, Excel will prompt you to enable the content again. To avoid this, you can change your Trust Center settings or save the file to a trusted location.

Once a particular workbook becomes a trusted document, there is no way to un-trust it. You can only clear the Trusted Documents list. For this, do the following:

  1. Click File > Options.
  2. On the left side, select Trust Center, and then click Trust Center Settings.
  3. In the Trust Center dialog box, select Trusted Documents on the left.
  4. Click Clear, and then click OK.

This will make all previously trusted files untrusted. When you open such a file, the security warning will show up.
Clearing all trusted documents

Tip. If you do not want to make any documents trusted, tick the Disable Trusted Documents box. You will still be able to turn on macros on opening a workbook, but only for the current session.

How to resolve "Microsoft has blocked macros"

In new versions of Excel, Microsoft has changed the behavior for macro-enabled files downloaded from the internet. Instead of the yellow Security Warning that allows enabling the content straight away, it now often displays a read Security Risk bar with this message: Microsoft has blocked macros from running because the source of this file is untrusted.

Security warning: Microsoft has blocked macros from running because the source of this file is untrusted.

Clicking Learn More won't unblock the macro – it just takes you to a web page explaining the reasons and solutions.

How to unblock a macro workbook in Excel

To unblock a macro that is blocked by Microsoft, this is what you need to do:

  1. Close the workbook containing the blocked macro.
  2. In File Explorer, browser to the location where the workbook is saved.
  3. Right-click the file, and select Properties from the context menu.
  4. In the Properties dialog box, check the Unblock box, and then click OK.

Unblock the macro that was blocked by Microsoft.

How to enable macros for one session

In some situations, it stands to reason to enable macros only for a single time. For example, when you received an Excel file with VBA code that you'd like to investigate, but you do not wish to make this file a trusted document.

The following instructions will guide you through the steps to enable macros for the duration that the file is open:

  1. Click the File tab > Info.
  2. In the Security Warning area, click Enable Content > Advanced Options.
  3. In the Microsoft Office Security Options dialog box, select Enable content for this session, and click OK.
    Enable macros for the current session.

This turns on macros for one time. When you close the workbook, and then reopen it, the warning will appear again.

How to enable macros in all workbooks via Trust Center

Microsoft Excel determines whether to allow or disallow VBA codes to run based on the macro setting selected in the Trust Center, which is the place where you configure all the security settings for Excel.

To get macros enabled in all Excel workbooks by default, this is what you need to do:

  1. Click the File tab, and then click Options at the very bottom of the left bar.
  2. On the left-side pane, select Trust Center, and then click Trust Center Settings… .
    Trust Center Settings
  3. In the Trust Center dialog box, click Macro Settings on the left, select Enable all macros and click OK.
    Enable all macros in Excel by default.

Notes:

  • The option you set via the Trust Center becomes the new default macro setting and applies globally to all of your Excel files. If you want to enable macros for only specific workbooks, save them in a trusted location instead.
  • Enabling all macros in all workbooks makes your computer vulnerable to potentially dangerous codes.

Excel macro settings explained

Bellow we will briefly explain all macro settings in the Trust Center to help you make an informed decision:

  • Disable all macros without notification - all macros are disabled; no warning will show up. You won't be able to run any macros except the ones stored in trusted locations.
  • Disable all macros with notification (default) - macros are disabled, but you can enable them on a case-by-case basis.
  • Disable all macros except digitally signed macros – unsigned macros are disabled with notifications. Macros digitally signed with a special certificate by a trusted publisher are allowed to run. If you have not trusted the publisher, Excel will prompt you to trust the publisher and enable the macro.
  • Enable all macros (not recommended) - all macros are allowed to run, including potentially malicious codes.
  • Trust access to the VBA project object model - this setting controls programmatic access to the object model of Visual Basic for Applications. It's disabled by default to prevent unauthorized programs from changing your macros or building self-replicating harmful codes.

When changing the Trust Center settings, please keep in mind that they apply only to Excel, not to all Office programs.

Enable macros permanently in a trusted location

Instead of manipulating the global macro settings, you can configure Excel to trust specific locations on your computer or local network. Any Excel file in a trusted location opens with macros enabled and without security warnings, even if the Disable all macros without notification option is selected in the Trust Center settings. This lets you run macros in certain workbooks when all other Excel macros are disabled!

An example of such files in the Personal Macro Workbook – all VBA codes in that workbook are available for you to use whenever you start Excel, regardless of your macro settings.

To view the current trusted locations or add a new one, carry out these steps:

  1. Click File > Options.
  2. On the left-hand pane, select Trust Center, and then click Trust Center Settings… .
  3. In the Trust Center dialog box, select Trusted Locations on the left side. You will see a list of the default trusted locations. These locations are important for the correct work of Excel add-ins, macros and templates, and should not be changed. Technically, you can save your workbook to one of the Excel default locations, but it's better to create your own one.
  4. To set up your trusted location, click Add new location… .
    Adding a new trusted location
  5. In the Microsoft Office Trusted Locations dialog box, do the following:
    • Click the Browse button to navigate to the folder that you want to make a trusted location.
    • If you wish any subfolder of the selected folder to be trusted too, check the Subfolders of this location are also trusted box.
    • Type a short notice in the Description field (this can help you manage multiple locations) or leave it empty.
    • Click OK.

    Choose the folder that you want to make a trusted location.

  6. Click OK twice to close the remaining dialog boxes.

Done! You can now place your workbook with macros in your own trusted location and do not bother about Excel's security settings.

Tips and notes:

  • Please be very careful when choosing a trusted location. Because Excel automatically enables all macros in all workbooks that are stored in trusted locations, they become kind of loopholes in your security system, vulnerable to macro viruses and hacking attacks. Never make any temporary folder a trusted source. Also, be cautious with the Documents folder, rather create a subfolder and designate it as a trusted location.
  • If you've mistakenly added a certain folder to the list of trusted locations, select it and click the Remove button.

How to enable macros programmatically with VBA

On Excel forums, many people ask if it is possible to enable macros programmatically on opening a workbook and disable them before exiting. The immediate answer is "No, it's not possible". Because macro security is critical for the security of Excel, Microsoft designed any VBA code to only be triggered by a user click.

However, when Microsoft closes a door, the user opens a window :) As a workaround, someone suggested a way to force the user to enable macros with a kind of "splash screen" or "instruction sheet". The general idea is as follows:

You write a code that makes all the worksheets but one very hidden (xlSheetVeryHidden). The visible sheet (splash screen) says something like "Please enable macros and re-open the file" or provides more detailed instructions.

If macros are disabled, the user can only see the "Splash Screen" worksheet; all other sheets are very hidden.

If macros are enabled, the code unhides all the sheets, and then makes them very hidden again when the workbook closes.

How to disable macros in Excel

As already mentioned, Excel's default setting is to disable macros with notification and allow users to enable them manually if they want to. If you'd like to disable all macros silently, without any notification, then choose the corresponding option (the first one) in the Trust Center.

  1. In your Excel, click the File tab > Options.
  2. On the left-side pane, select Trust Center, and then click Trust Center Settings… .
  3. In the left menu, select Macro Settings, choose Disable all macros without notification, and click OK.
    Disabling macros in Excel

That's how you can enable and disable macros in Excel. I thank you for reading and hope to see you on our blog next week!

Table of contents

37 comments

  1. My excel file does not have a file tab.
    How can I disable the macros in another way?

  2. Thank you so much! I had read somewhere else to right click on the file and go to “properties.” I obviously didn’t have much success with that….. You quickly solved my problem! Thank you so much!! This one little tiny problem was about to ruin a PowerPoint presentation that was going in my online portfolio as part of my transition to a new career. I was about to lose it!!! Thank you so much!! Sometimes it’s the small things that we need to make the BIG things possible!! Have a great day!!!

  3. Hello, the content is very good and perfectly written.

  4. What if I want to share this excel sheet with many people, they need to add location of where they can save the file to make the code work ?

  5. PERFECT! THANK U SO MUCH

  6. Hi,

    I've been looking for months for a way to disable macros when a formula has a certain value (i.e. IF(A2=1, disable all macros). Can it be done? Thanks in advance

      • Wow! It's good to know that it can be done but the VBA file in question has over 20 macros and the files are to be sent individually. What about downloadable VBA programs? Can they be downloaded safely via SamCart (or a similar service provider) or does it require something to do with the Trust Center? The idea is to have a 10-day demo which expires by date, but changing the system's date overrides this measure. The tabs are hidden and the sheets can only be accessed by macros. Disabling them would in turn 'override' the date changes. Thanks for your help. It's a five-year project nearing culmination. I could send you a video of the program. Don't think I can post the YouTube link here. Thanks again.

  7. Hi there
    I have an issue where the Microsoft Excel Security Notice box pops up but only shows disable Marcos
    I do not have the option to enable

    I have used the same settings as a working machine where the pop up has both the enable and disable options
    What can I change to get the Enable back

  8. I have tried everything on this and i still cannot get my macros to run. the excel macro enabled workbook is in a local directory that is set explicitly to trusted location in trust centre. the VBA macros are set to enabled in the macro settings in the trust centre. Still i cannot get the macros to run. I have also tried to right click the file in file explorer to set the file to enable macros but cannot see the option that is described in many internet posts. This is so frustrating. I have spent 4 hours on this simple thing trying to get it to work. Any suggestions anyone ?

    • Hi,

      Recently had the same issue, found this buried in the Microsoft faqs it worked for me but my issue was because the file was being stored on one drive

      Files on OneDrive or SharePoint

      If a user downloads a file on OneDrive or SharePoint by using a web browser, the configuration of the Windows internet security zone (Control Panel > Internet Options > Security) will determine whether the browser sets Mark of the Web. For example, Microsoft Edge sets Mark of the Web on a file if it's determined to be from the Internet zone.

      If a user selects Open in Desktop App in a file opened from the OneDrive website or from a SharePoint site (including a site used by a Teams channel), then the file won't have Mark of the Web.

      If a user has the OneDrive sync client running and the sync client downloads a file, then the file won't have Mark of the Web.

      Files that are in Windows known folders (Desktop, Documents, Pictures, Screenshots, and Camera Roll), and are synced to OneDrive, don't have Mark of the Web.

      If you have a group of users, such as the Finance department, that need to use files from OneDrive or SharePoint without macros being blocked, here are some possible options:

      Have them open the file by using the Open in Desktop App option

      Have them download the file to a Trusted Location.

      Set the Windows internet security zone assignment for OneDrive or SharePoint domains to Trusted Sites. Admins can use the "Site to Zone Assignment List" policy and configure the policy to place h ttps://{your-domain-name}.sharepoint.com (for SharePoint) or h ttps://{your-domain-name}-my.sharepoint.com (for OneDrive) into the Trusted Sites zone.

      This policy is found under Windows Components\Internet Explorer\Internet Control Panel\Security Page in the Group Policy Management Console. It’s available under both Computer Configuration\Policies\Administrative Templates and User Configuration\Policies\Administrative Templates.

      SharePoint permissions and OneDrive sharing aren't changed by adding these locations to Trusted Sites. Maintaining access control is important. Anyone with permissions to add files to SharePoint could add files with active content, such as macros. Users who download files from domains in the Trusted Sites zone will bypass the default to block macros.

  9. Hi.. your post is very knowledgeable.. I have changed a trusted location in the share drive folder as I am also facing an issue that while the 1st-time macro is working fine but after some time it's getting the same error.

  10. Hi.. your post is very knowledgeable.. I have 1 doubt can I make trusted location as in share drive folder as I am also facing issue that while 1st time macro is working fine but on 2nd time it is disabled.. please response asap

  11. Just wanted to say... You post great content. Have a Merry Christmas ?

    Aloha from Hawaii?

  12. when i try to open a particular excel i use for reconciliation i get i get this pop up- ,MARCROS IN THIS DOCUMENT HAVE BEEN DISABLED BY YOUR ENTERPRISE ADMINISTRATOR. how do i enable it

    • Hi Alex,

      According to Microsoft docs, this issue may occur if a workbook isn't from a trusted location. The recommended solution is to save the workbook to a trusted location.

      • which version used right now?

  13. there is no "option" under file on my excel - so how do I get to the Trust center?

    • Hello Barbara,

      What Excel version do you use? In Excel 2010 - 2019 as well as Excel 365, clicking the File tab takes you to the Backstage view, and the Options button is at the very bottom of the left pane.

  14. Hello,
    I have got a problem with excel file (all files), all the options under trust center settings are grayed out, like Macro settings, Trusted locations, protected view etc. Please help me on how to enable the settings.
    Thank you

  15. it wont work

  16. the Security Warning did not appear in "file" - "Info". can you help?

    • Macro

  17. how do i turn on macro im doing it for fun

  18. How do you open macro (Solver) in XLAM format
    Thanks.

  19. I am trying to run Worksheetfunction.search and Worksheetfunction.find in VBA (Microsoft 365) but get "unable to get the Match property of the WorksheetFunction class". Is there an add-in I need?
    BTW: Worksheetfunction.Text works.

    Thanks.

  20. In My computer Macro security option and recording and other option is disbale.i dont know why even instaled new Office

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)