Skip to content
Related Articles

Related Articles

Improve Article

How to Enable and Disable Macros in Excel?

  • Last Updated : 01 Jun, 2021

A macro is a set of commands that you can use to automate a repetitive task and run whenever you need to. This article discusses the risks associated with working with macros, as well as how to enable or disable macros in the Trust Center. Using macros in Excel reduces human error and saves time by automating repetitive tasks.

Macro security in Excel

Before you enable macros in your worksheets, you should understand how dangerous they can be.

Though VBA (Visual Basic for Applications) codes are extremely effective at automating complex and repetitive tasks, they pose a significant security risk. Unintentionally running a malicious macro can damage or completely delete files on your hard drive, corrupt your data, and even corrupt your Microsoft Office installation. As a result, the default setting in Excel is to disable all macros with notification.

How can these dangers be avoided? Simply follow one simple rule: enable only safe macros – those you’ve written or recorded yourself, macros from reliable sources, and VBA codes you’ve thoroughly reviewed and comprehended.

How to Enable macros for individual workbooks?

Macros can be enabled for a specific file in two ways: directly from the workbook and via the Backstage view.



1. Enable macros directly via security notice:

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

The Microsoft Excel Security Notice will be displayed if the Visual Basic Editor is open at the time you open the file with macros:

If you are confident in the file’s origin and that all macros are safe, click the Enable Content or Enable Macros button. This activates the macros and marks the file as a trusted document. 

2. Turn on macros in Backstage view:

The Office Backstage view is another way to enable macros for a specific workbook. Here’s how to do it:



  • Click the File tab, then Info from the left menu.
  • Click Enable Content > Enable All Content, in the Security Warning area.

Your workbook, like the previous method, will become a trusted document.

How to Enable macros for one session?

In some cases, enabling macros for a single instance makes sense. For example, suppose you received an Excel file containing VBA code that you want to investigate but do not want to make it a trusted document.

The steps below will walk you through the process of enabling macros for the duration of the file’s open state:

  1. Navigate to the File tab > Info.
  2. Click Enable Content > Advanced Options in the Security Warning area.
  3. Select Enable content for this session in the Microsoft Office Security Options dialogue box, then click OK.

This enables macros for a single-use. The warning will reappear if you close and then reopen the workbook.

How to Enable macros in all workbooks through the Trust Center?

Microsoft Excel decides whether to allow or disallow VBA code execution based on the macro setting selected in the Trust Center, which is where you configure all of Excel’s security settings.

To enable macros by default in all Excel workbooks, follow these steps:

  • Navigate to the File tab and then to Options.
  • Select Trust Center from the left-hand pane and then click on Trust center setting.

  • In the Trust Center dialog box, click Macro Settings on the left, select Enable all macros and click OK.



Notes: The option you select in the Trust Center becomes the new default macro setting for all of your Excel files. Instead, if you only want to enable macros for specific workbooks, save them in a trusted location.

How to Enable macros permanently in a trusted location?

Rather than tampering with the global macro settings, you can instruct Excel to trust specific locations on your computer or local network.

Follow these steps to view the current trusted locations or to add a new one:

  • Navigate to File > Options.
  • Select Trust Center from the left-hand pane, and then click Trust Center Settings.
  • On the left side of the Trust Center dialogue box, select Trusted Locations. You will be presented with a list of the default trusted locations. These locations are critical for the proper operation of Excel add-ins, macros, and templates and should not be altered. Technically, you can save your workbook to one of Excel’s default locations, but it’s preferable to create your own.
  • Click Add a new location to set up your trusted location.

  • Do the following in the Microsoft Office Trusted Locations dialogue box:
    • Navigate to the folder you want to make a trusted location by clicking the Browse button.
    • Check the Subfolders of this location are also trusted box if you want any subfolder of the selected folder to be trusted as well.
    • Fill in the Description field with a brief message (this can help you manage multiple locations) or leave it blank.
    • Click the OK button.

Completed! You can now save your workbook with macros in a trusted location without worrying about Excel’s security settings.

How to Enable macros programmatically with VBA? 

Many people ask on Excel forums if it is possible to enable macros programmatically when opening a workbook and disable them before exiting. “No, it’s not possible,” is the immediate response. Because macro security is critical for Excel’s security, Microsoft designed any VBA code to be activated only by a user click.

When Microsoft closes a door, the user opens another 🙂 As a workaround, someone suggested using a “splash screen” or “instruction sheet” to force the user to enable macros. The fundamental concept is as follows:

You write code that generates all the worksheets except one, which is tucked away. The visible sheet (splash screen) states something like “Please enable macros and re-open the file” or provides more specific instructions.

When macros are disabled, the user can only see the “Splash Screen” worksheet; all other sheets are hidden. When macros are enabled, the code unhides all the sheets before re-hiding them completely when the workbook is closed.

How to Disable macros in Excel?

As previously stated, Excel’s default setting disables macros with a notification and allows users to enable them manually if desired. If you want to disable all macros silently, without receiving any notifications, go to the Trust Center and select the corresponding option (the first one).

  1. Click the File tab > Options in Excel.
  2. Select Trust Center from the left-hand pane, and then click Trust Center Settings.
  3. Select Macro Settings from the left menu, then Disable all macros without notification and click OK.

Attention reader! Don’t stop learning now. If you are an Excel beginner (or an intermediate) and want to learn Excel, Geeksforgeeks brings the perfect course for you to start, Diving Into Excel




My Personal Notes arrow_drop_up
Recommended Articles
Page :