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

Unblock Macros downloaded from the internet

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

Microsoft are making changes so that any office files downloaded from the internet which contain macros will be blocked by default.  While it may be frustrating to users, it ensures they stay safe online, which is of critical importance.

This change has started to roll out to those on the Beta/Insiders channel of the Office 365 subscription. Once Microsoft are happy this is working correctly, I’m sure it will be rolling out to other versions too. The applications affected are Access, Excel, PowerPoint, Word, and Visio.

As there are a lot of downloadable example files on the site that contain macros; therefore, I wanted to let you know how to unblock those files and to make sure you stay vigilant online.

Watch the video


Watch the video on YouTube.

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, there was a warning which required the user to Enable Content.

Macros disabled button

Unfortunately, too many users were clicking Enable Content without considering whether the file is safe.

The new behavior that users will see is a message: “Microsoft has blocked macros from running because the source of this file is untrusted”. This will be the same message for most files downloaded from the internet, so even safe macros will have this warning.

Macros blocked security message

Clicking Learn More should take 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

By adding extra steps to unblock files, Microsoft is hoping that users will only enable files they trust.

The steps to unblock a file are:

  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 will be unblocked and the macros can now be run. It is essential to only unblock files from sources that you trust.

Trusted locations

Any workbooks saved in trusted locations will not be blocked by default. To find out which locations are trusted, or to add new trusted locations, go to File > Options > Trust Center Settings… > Trusted Locations

Trusted Locations

Obviously, take care to ensure files downloaded from the internet are not saved directly into a trusted location, as that will bypass this macro security feature.

What about Add-ins?

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

Conclusion

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

While this is not the end of macros, this is certainly a step that will make it harder to use them. 



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


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet 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:

5 thoughts on “Unblock Macros downloaded from the internet

  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)

Leave a Reply

Your email address will not be published. Required fields are marked *