Unblocking and Enabling Macros

When Windows detects that a file has come from a computer other than the one you’re using, it marks the file as coming from the web, and blocks the file. This includes files emailed to you and files downloaded from the web. Here is how blocked files behave, and the steps you must take to unblock macros.

Blocked Files

If you open a blocked file, Excel opens it in Protected View. The ribbon is hidden, and a warning appears across the top of the window. You can view but not edit the workbook.

When you click on Enable Editing, Excel reopens the workbook normally. But since it contains VBA, you get a message that macros are blocked. You can dismiss the message, but you can’t run macros from the workbook.

If the VBA Editor window is open, instead of the Security Risk message, you get a Security Notice informing you that macros are blocked, and your only option is to Disable Macros.

Close the file and unblock it as shown below.

Blocked Add-Ins

Blocked add-ins exhibit their own strange behaviors. If you try to install a blocked add-in, you may see a message that Protected View is not supported for that document type. Or you may have been able to install the add-in, but now, although it is checked in the Add-Ins dialog, it doesn’t appear as expected in Excel’s ribbon. If you experience issues like this, try unblocking the add-in using the same protocol below, then install it as usual. These steps are also outlined in my article, Install an Excel Add-In.

Unblocking Files

To unblock a file that came from the web, find the file in Windows File Explorer. If you unblock a zip file, all files within the zip file will be unblocked.

Right-click on the file and click on Properties at the bottom of the context menu that pops up. This opens the Properties dialog. Note the warning, click to check the Unblock checkbox, and click OK.

Excel opens the workbook normally, but since it contains VBA, you get a Security Warning that macros have been disabled. Click the Enable Content button to enable macros.

If the VBA Editor window is open, instead of the Security Warning, you get a Security Notice informing you that macros are blocked, and you have the option to Enable Macros.

Now you’re ready to roll.

What if this Doesn’t Work?

The protocol above works for the most common security settings. But your IT department may have imposed different, more stringent settings. If IT has blocked macros, you could talk to them about it, or maybe just quit your job.

Macro Settings

To find your settings, go to File > Options, and click on Trust Center.

Click the Trust Center Settings button, then click on Macro Settings.

This is my preferred setting:

• Disable VBA Macros With Notification will open with macros disabled but provide an option to enable macros. This is my preferred option.

Here are the other options:

• Disable VBA Macros Without Notification will open with macros disabled and provide no option to enable macros, and won’t even tell you about the macros.
• Disable VBA Macros Except Digitally Signed Macros will open with macros disabled unless the workbook has been digitally signed and the associated certificate has been trusted.
• Enable VBA Macros will enable any and all macros without any notification. DO NOT USE THIS OPTION.

There are a couple of other items you can think about, which don’t affect VBA being blocked.

• Enable Excel 4.0 Macros when VBA Macros Are Enabled enables the old macros when the new macros are enabled. I keep it checked because I feel that I’ve already trusted the document when I clicked on Enable Macros.
• Trust Access to the VBA Project Object Model will allow VBA macros to modify VBA projects, that is, write code and modify other settings. Check this box if you are developing VBA code that needs these capabilities, but in general you should probably keep it unchecked.

S Mode

On some new computers, Windows 11 comes installed in S Mode, which is an ultra-secure mode. You may not even be allowed to download a file that contains VBA, and Windows will inform you incorrectly that the file has some kind of evil virus. I believe S Mode is limited to the Home edition of Windows, so you can deal with it yourself. Removing S Mode is a simple operation, then you can download and unblock files as described above.

References

A potentially dangerous macro has been blocked

Macros from the internet will be blocked by default in Office