How to Enable and Disable Macros in Excel: A Complete Guide
Last updated on April 19, 2023 By Victor Chan
Macros are a powerful feature in Excel that can help you automate repetitive tasks, save time, and improve your productivity. But macros can also pose a security risk if they come from untrusted sources or contain malicious code. That's why Excel disables macros by default and warns you before running them. You must manually enable macros to use them.
In this article, you'll learn how to enable and disable macros in Excel for Windows and Mac, how to adjust the macro security settings, and how to create a trusted location for your macro-enabled workbooks. You'll also learn some best practices for working with macros safely and effectively.
What are VBA macros in Excel?
A macro is a series of commands or instructions that can be executed with a single click or keystroke. Macros can perform various actions, such as formatting cells, inserting formulas, copying data, creating charts, and more.
You can create VBA macros in Excel by using the Macro Recorder or by writing VBA (Visual Basic for Applications) code in the Visual Basic Editor. You can also import macros from other sources, such as online forums, blogs, or books.
Macros are stored in macro-enabled workbooks, which have the .xlsm file extension. You can also store VBA macros in a personal macro workbook, which is a hidden workbook that loads every time you open Excel. This way, you can access your VBA macros from any workbook.
Why are VBA macros disabled in Excel?
Macros are disabled in Excel because they can potentially run harmful code on your computer or network. For example, a malicious macro could delete or corrupt your files, steal your data, install malware, or access your personal information.
Excel uses a security feature called the Trust Center to protect you from unsafe VBA macros. The Trust Center allows you to control how VBA macros are handled in Excel and what level of security you want to apply.
By default, Excel disables all VBA macros with notification. This means that when you open a workbook that contains VBA macros, you'll see a yellow security warning bar at the top of the sheet. You can choose to enable or disable VBA macros for that workbook by clicking on the warning bar.
If you don't see the warning bar, it means that either there are no VBA macros in the workbook or that the workbook is already trusted by Excel.
How to enable and disable macros in Excel for Windows?
There are two ways to enable and disable macros in Excel for Windows: through the security warning bar or through the Backstage view.
Enable and disable macros via security warning bar
The easiest way to enable and disable macros is via the security warning bar that appears when you open a macro-enabled workbook. Here's how:
- 1. Open any Excel workbook that contains macros.
- 2. If you see a yellow security warning bar at the top of the sheet, click on it.
- 3. You'll see two options: Enable Content and Disable Content.
- 4. If you trust the source of the workbook and want to run the macros, click Enable Content. This will turn on the macros and make the workbook a trusted document. The next time you open it, you won't see the warning bar.
- 5. If you don't trust the source of the workbook or don't want to run the macros, click Disable Content. This will keep the macros disabled and prevent them from running.
- 6. If you change your mind later, you can re-open the workbook and click on the warning bar again to enable or disable macros.
Enable and disable macros via Backstage view
Another way to enable and disable macros is via the Backstage view. Here's how:
- 1. Click on the File tab on the ribbon.
- 2. Click on Info in the left menu.
- 3. On the right pane, you'll see a section called Security Warning with a button that says Enable Content or Disable Content depending on whether the macros are enabled or disabled.
- 4. Click on that button to toggle between enabling and disabling macros for that workbook.
- 5. You can also click on Edit Anyway if you want to edit the workbook without enabling or disabling macros.
Macros from the internet are blocked by default in Office for Windows
Just a heads-up about VBA macros in Office apps. They're sometimes used by not-so-nice people to spread malware and ransomware, so Microsoft decided to make Office safer. Currently this only affects Office on devices running Windows, and for these apps: Access, Excel, PowerPoint, Vision, and Word.
They changed how Office for Windows apps handle macros in files from the internet. From 2022 or 2023 (depending on which channel you use), if you open a file that has macros and comes from the internet (like an email attachment), you'll see a friendly message like this:
You can learn the full details from Microsoft here: Internet Macros Blocked
How to enable and disable macros in Excel for Mac?
The process of enabling and disabling macros in Excel for Mac is similar to Windows, but with some minor differences.
Enable and disable macros via security warning dialog
When you open a macro-enabled workbook for Mac, you'll see a pop-up dialog that warns you that the file contains macros. You can choose to enable or disable macros for that workbook by clicking on the dialog.
- 1. Double-click the file you want to open as you normally would.
- 2. You'll see a pop-up dialog that says "This workbook contains macros. Do you want to enable macros?" with two options: Enable Macros and Disable Macros.
- 3. If you trust the source of the workbook and want to run the macros, click Enable Macros. This will turn on the macros and make the workbook a trusted document. The next time you open it, you won't see the dialog.
- 4. If you don't trust the source of the workbook or don't want to run the macros, click Disable Macros. This will keep the macros disabled and prevent them from running.
- 5. If you change your mind later, you can re-open the workbook and click on Tools > Macro > Security on the menu bar to enable or disable macros.
Enable and disable macros via Backstage view
When you open a macro-enabled workbook for Mac, you'll see a pop-up dialog that warns you that the file contains macros. You can choose to enable or disable macros for that workbook by clicking on the dialog.
- 1. Click on the File tab on the menu bar.
- 2. Click on Properties in the left menu.
- 3. On the right pane, you'll see a section called Security with a button that says Enable Content or Disable Content depending on whether the macros are enabled or disabled.
- 4. Click on that button to toggle between enabling and disabling macros for that workbook.
- 5. You can also click on Edit Anyway if you want to edit the workbook without enabling or disabling macros.
How to change macro security settings in Excel?
By default, Excel disables all macros with notification. This means that you'll see a warning message every time you open a workbook that contains macros, and you can choose whether to enable or disable them.
However, you can change this setting to suit your preferences and needs. For example, you can disable all macros without notification, disable all macros except digitally signed ones, or enable all macros (not recommended).
To change macro security settings in Excel, you need to access the Trust Center. The Trust Center is like a hub for managing security and privacy settings in Excel and other Microsoft Office programs.
Here's how to change macro security settings in Excel:
- 1. Click on the File tab on the ribbon (or Tools > Macro > Security on the menu bar for Mac).
- 2. Click on Options in the left menu (or Security Level tab for Mac).
- 3. Click on Trust Center in the left menu (or Macro Settings tab for Mac).
- 4. Click on Trust Center Settings in the right pane (or skip this step for Mac).
- 5. Click on Macro Settings in the left menu.
- 6. You'll see four options for macro settings:
- Disable all macros without notification: This option disables all macros silently, without showing any warning messages. This is the most secure option, but it also prevents you from running any macros, even if they are safe or trusted.
- Disable all macros with notification: This is the default option that disables all macros but shows a warning message when you open a workbook that contains macros. You can choose to enable or disable macros for each workbook by clicking on the warning message.
- Disable all macros except digitally signed macros: This option disables all macros except those that are digitally signed by a trusted publisher. A digital signature is a way of verifying the identity and integrity of a macro source. If a macro is digitally signed by a publisher you trust, it will run automatically without any warning messages. If a macro is signed by a publisher you haven't trusted yet, you'll see a warning message that gives you an option to enable the macro and trust the publisher. If a macro is not signed or has an invalid signature, it will be disabled and you'll see a warning message that gives you an option to enable the macro without trusting the publisher. Learn how to digitally sign your VBA macro projects on Microsoft's support site.
- Enable all macros (not recommended, potentially dangerous code can run): This option enables all macros without any warning messages. This is the least secure option and should be used only if you are sure that all macros are safe and trusted. Enabling this option can expose your computer or network to malicious code.
- 7. Click OK to save your changes and close the Trust Center.
Note: There is one more setting available in the Trust Center, “Trust access to the VBA project object model”.
When this option is enabled, it allows VBA code to interact with and manipulate the VBA project object model. This can be useful for automating tasks such as adding, modifying, or deleting VBA modules, user forms, or references.
However, enabling this setting can also expose your computer to potential security risks, as malicious code could potentially access and modify your VBA projects.
If you’re a VBA beginner, I strongly advise you not to turn this setting on.
How to create a trusted location for macros in Excel?
A trusted location is a folder that contains files that you trust to be safe. Files in a trusted location are not checked by the Trust Center and do not open in Protected View or Application Guard. All macros in a trusted location are enabled automatically without any warning messages.
You can create your own trusted locations to store your macro-enabled workbooks or workbooks from reliable sources. However, you should be careful when choosing a trusted location and make sure that only you or authorized people can access it. You should also avoid using network locations or removable media as trusted locations, as they can be more vulnerable to attacks.
To create a trusted location for macros in Excel, follow these steps:
- 1. Click on the File tab on the ribbon (or Tools > Macro > Security on the menu bar for Mac).
- 2. Click on Options in the left menu (or Security Level tab for Mac).
- 3. Click on Trust Center in the left menu (or Macro Settings tab for Mac).
- 4. Click on Trust Center Settings in the right pane (or skip this step for Mac).
- 5. Click on Trusted Locations in the left menu.
- 6. You'll see a list of default trusted locations that are created by Excel or other Office programs. You can modify or remove these locations if you want, but it's not recommended.
- 7. To add a new trusted location, click on Add new location.
- 8. In the pop-up window, click on Browse to find the folder that you want to designate as a trusted location.
- 9. Select a folder and click OK.
- 10. You can also enter a description for the folder and check some options, such as:
- Subfolders of this location are also trusted: This option allows you to trust all subfolders within the selected folder.
- Allow Trusted Locations on my network (not recommended): This option allows you to trust network locations, such as shared drives or servers. This is not recommended because network locations can be more prone to security breaches.
- Date created: This option shows you when the trusted location was created.
- 11. Click OK to add the trusted location and close the window.
- 12. Click OK to close the Trust Center.
You can now save and open your macro-enabled workbooks in the trusted location without any security warnings. Just be careful of what you save there, especially files from an internet source that you don’t trust.
Conclusion: Enable Macros and Stay Vigilant
Macros are a useful feature in Excel that can help you automate tasks and enhance your workbooks. However, macros can also pose a security risk if they come from untrusted sources or contain harmful code.
By default Excel disables macros for your protection and you need to enable macros to use them. This applies to Excel for Windows, Excel for Mac, and Excel 365.
To protect yourself from unsafe macros, you should use the Trust Center to control how macros are handled in Excel and what level of security you want to apply. You should also use trusted locations to store your macro-enabled workbooks or workbooks from reliable sources.
By following these tips, you can work with macros safely and effectively in Excel.
Stay safe!
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.