Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


How to fix “Microsoft has blocked macros” (2 simple ways)

You are probably here because you’ve seen a warning message: Microsoft has blocked macros from running because the source of this file is untrusted. This post provides the background to this message and the steps you should take to enable the file.

During 2022, Microsoft introduced new security into the Windows version of Microsoft Excel to protect users against malicious Excel macros. Due to a significant increase in attempts by hackers to use macros, Microsoft had to take steps to protect users.

It is very difficult to identify VBA code that is legitimate and code which is not. Therefore, the security Microsoft introduced was so wide-reaching that it impacted all Excel users.

Even files with completely safe macros receive the same warning message; therefore, it is not necessarily anything to worry about.

Watch the video


Watch the video on YouTube.

Overview

Macros are a great way to automate Excel; they give us a lot of power to interact with, not just Excel but Windows and other Office applications. Unfortunately, hackers know about this power too, and use files containing macros to distribute malware to PCs and entire networks.

The change introduced by Microsoft automatically blocks all macros in workbooks downloaded from the internet. While it is frustrating to users, it is to ensure online safety, which is of critical importance.

This security change does not just affect Excel; other Office applications are affected too: Access, PowerPoint, Word, and Visio.

Since macros are used to increase productivity by automating Excel, I wanted to let you know how to unblock files. Once they are unblocked, you can use them as usual. This post provides 2 simple ways to unblock macros, so that you don’t receive the Microsoft has blocked macros from running because the source of this file is untrusted message again.

What will you see?

Files downloaded from the internet using the Windows operating system have an attribute added to them, known as the Mark of the Web (MOTW).

Previously, when users opened macro files with the MOTW, a warning appeared that required the user to click Enable Content.

Macros disabled button

Unfortunately, too many users clicked Enable Content without considering whether the file was safe.

With the new behavior, users see a message: “Microsoft has blocked macros from running because the source of this file is untrusted”. This will be the same message for all macro-enabled files downloaded from the internet; therefore, even safe macros trigger this warning.

Macros blocked security message

Clicking Learn More takes you to a web page detailing the reasons for this message and provides instructions on how to enable the file for use: https://support.microsoft.com/en-gb/topic/a-potentially-dangerous-macro-has-been-blocked-0952faa0-37e7-4316-b61d-5b5ed6024216

How to unblock a macro workbook (Method #1)

The first method is to unblock the file. This requires a few extra clicks. By adding these additional steps, Microsoft hopes users will only enable files they trust.

The steps to unblock a file are as follows:

  1. Close the workbook
  2. Navigate to the location where the workbook is saved
  3. Right-click on the file and select Properties from the menu
    Right-click properties
  4. Check the Unblock box, then click OK
    Unblock OK

Now the workbook is unblocked, and the macros can run. It is essential to only unblock files from sources that you trust.

That was simple enough; it only required a few additional clicks. However, as we perform this file-by-file, it could become time-consuming for many users. So, next, we look at another method that removes the block for lots of files.

Trusted locations (Method #2)

The second method uses a “trusted location”. Any workbooks saved in trusted locations bypass the macro security and are not blocked by default.

To find out which locations are trusted or add new ones, go to File > Options > Trust Center Settings… > Trusted Locations

Trusted Locations

To add a new trusted location, click the Add new location… button and follow the on-screen prompts.

Once set up, trusted locations are easy to use. Just save the file in the trusted folder, and open it as usual. The warning message should not appear, and you can use the file.

Obviously, take care only to save files you trust in a trusted location.

What about Add-ins?

Excel VBA Add-ins behave in the same way as before the security change. It has been necessary to unblock add-ins for several years; therefore, this behavior has not changed.

Conclusion

Online security is critical, so Microsoft has taken steps to help users stay safe. While you may not have the feature that blocks downloaded macros yet, Microsoft indicated that it will roll out this feature to Excel 2013 and later (https://docs.microsoft.com/en-gb/DeployOffice/security/internet-macros-blocked).

At least now, if you receive the Microsoft has blocked macros… warning again, you will know what to do.

Did either method in this post fix your problem? Let us know in the comments. Also, please comment if you have an alternative approach to fix this issue.


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

9 thoughts on “How to fix “Microsoft has blocked macros” (2 simple ways)

  1. mick says:

    This is a nightmare. All of my workbooks use extensive VBA and macros and are stored on Microsoft SharePoint. i have tried to save my workbook locally, tried the UNBLOCK check box in properties and the security box disappears (as it should). However, The next time i try to open my workbook i get the same BLOCKED CONTENT message.

    I have set the URL for my sharepoint storage as a trusted site (+ sub folders). And still no change.

    The only way that i can open my workbooks to fire the VBA is to accept ALL VBA. this is surely going against everything that this is trying to achieve as i would then be open to dodgy macros from excel documents and code that i haven’t written.

  2. Hubi says:

    Encountered this problem as well at my workplace. After some trying out we managed to find a solution by creating a free certificate and adding that certificate to the trusted publishers and trusted root Certificate Authority. Now signing the VBA code with that certificate and voilà it is working again. This only works inside a company else you will have to purchase a certificate from the offical partners of Microsoft.

    Steps to take (my interface is in german so maybe some translations are not accurate):
    1. Launch SelfCert.exe (commonly found at C:\Programme\Microsoft Office\root\Office16\SelfCert.exe)
    2. Create the certificate and choose a meaningful certificate name
    3. Go to the certificate manager and export that certificate.
    4. Make your admin add that certificate to trusted trusted Root Certificate Authority and trusted publishers
    5. Sign the VBA code with that certificate (in the VBA Editor > Extras > Digital Signature)

  3. Kevin G says:

    I’m going crazy because I keep getting messages that macors are blocked and also that I can run add-ins.

    I’ve done all the things everyone has suggested

    1) Method 1 above does not work because there is no “unblock” box to check
    2) I’ve tried method 2 and it doesn’t work either

    When I open a sheet with addins and macros I wrote, I get two messages

    1) “Some active content has been disabled, Click for more details.” When I click on this I get a box “security warning” with an “enable content” button. When I click on that I get an “advanced options” button. When I click on that, I get a box “Security alerts” multiple options. All this tells me I can’t run the addins and that “this setting is enforced by your sytem administrator and cannot be changed.” But I am a stand alone PC and there is no system administrator. Up until this started, I never saw such a message

    Also, the add-ins do not appear in the ribbon up top even though, if I go to Options/add-ins, the ones I want are checked.

    2) The other message says “Blocked Content Trusted document settings have changed, not all content in this file is allowed to run. Review your trust center settings or contact your system administrator” I’ve reviewed the trust center settings and macros are enabled and the location is trusted. If I go to view macros, they are listed but I can’t access them and they won’t run. So macros are clearly enabled but I still can’t run them.

    I have wondered if I have a virus, but my scanner says no. Keep in mind that these add-ins and macros all worked until recently.

    • Excel Off The Grid says:

      I suspect that it is related to these security changes. I’ve not seen the specific messages that you’ve had, so I can’t provide any more insight. Hopefully somebody else is experiencing the same issue and can provide more guidance.

Leave a Reply

Your email address will not be published.