Open In App

How to Enable and Disable Macros in Excel

Improve
Improve
Like Article
Like
Save
Share
Report

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.

Excel Macro Security

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 notifications Macros.

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:

Click on Enable content

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

Select Enbale 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 the Backstage View

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

Step 1: Click the File tab, then Info from the left menu.

Step 2: Click Enable Content > Enable All Content, in the Security Warning area.

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

Enable All content.

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 single use. The warning will reappear if you close and then reopen the workbook.

Enable macros for one session

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 the Trust Center setting.

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

Macro- Setting

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.

Select 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.

MS Office Trusted Location.

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.

Macro Setting.

FAQs

Define Macros in Excel?

A macro is a set of actions that we can run as many times as required to perform a particular task. When Macro is created, the whole mouse clicks and the keystrokes get recorded.

How to resolve “Microsoft has blocked Macros”?

In the latest versions of Excel, Microsoft has changed the behavior of macro-enabled files. Instead of the yellow Security Warning that allows enabling the content straight away, it now often displays a read security Risk bar with the message: Microsoft has blocked macros from running because the source of this file is intrusted.

Clicking on the Learn more won’t unblock the macro -it just takes you to a webpage explaining the reasons and solutions.

How to unblock the Macro Workbook in Excel?

Follow the steps to unblock the Macro Workbook

Step 1: Close the workbook containing the blocked macro.

Step 2: In the file explorer, browser to the location where the workbook is saved.

Step 3: Right- click the file, and select properties from the context menu.

Step 4: In the properties dialog box, check the unblock box, and then click OK.

Is it safe to enable macros in Excel?

It is safe to enable only those macros that come from trusted sources or have been self-recorded by the user. All other macros that come from unknown and non-rusted sources pose an inherent security risk.



Last Updated : 19 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads