Automatically Enabling Macros for Specific Workbooks

Written by Allen Wyatt (last updated February 7, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


9

Raymond has a small number of Excel workbooks that he works with every month, and these workbooks include macros. It is a real pain for him to enable the macros to run when he opens the workbooks, and sometimes he hits the wrong button and doesn't get the macros enabled. He wonders if there is a way to always open these particular workbooks with enabled macros. He doesn't want all workbooks to be automatically enabled, just these particular workbooks.

The "enable macros?" message you see when you open a macro-enabled workbook is generated by Excel based on the settings you've made in the Trust Center. You can see the Trust Center by displaying the Developer tab of the ribbon and, in the Code group, clicking the Macro Security tool. (See Figure 1.)

Figure 1. The Trust Center dialog box.

I generally suggest that the second option (Disable All Macros with Notification) be the security level used, and I suspect that this is the same level that Raymond has selected. (Were it not so, Raymond would not see an "enable macros?" notification when opening the workbook.)

It is possible to choose a more permissive security level in the Trust Center, but Raymond specifically said he did not want to do that.

There are two ways around this issue. The first is that you can store your macro-enabled workbook (the one you want to open without the message) in what is called a trusted location. Note that at the left of the Trust Center dialog box there is a Trusted Locations option. Click that, and you can see what locations Excel believes are trusted. (See Figure 2.)

Figure 2. The Trusted Locations portion of the Trust Center.

Check out what folders are currently set up as trusted locations, as you can always store your workbook in one of those. If you'd like, you could always use the controls in the dialog box to add another trusted location and then store your workbook in that folder. Anything stored in a trusted location "bypasses" (so to speak) the Trust Center checks, so you won't see the "enable macros?" notice. You can find more information about making modifications to trusted locations at this website:

https://support.office.com/en-us/article/add-remove-or-change-a-trusted-location-7ee1cdc2-483e-4cbb-bcb3-4e7c67147fb4

Using trusted locations is great on your own system, and will thus probably help out with Raymond's issue. If you are creating macro-enabled workbooks you want to run seamlessly on other people's systems, then you should think strongly about digitally signing your VBA project. This is more involved than simply changing trusted locations, though. You can find more information about digital signatures at this page:

https://support.office.com/en-us/article/Digitally-sign-your-macro-project-956e9cc8-bbf6-4365-8bfa-98505ecd1c01

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8265) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Quickly Moving Text with the Mouse

Drag-and-drop editing is a handy feature when you love to use the mouse. There are two ways you can move text using the ...

Discover More

Slowing Down Mouse Selection

Ever tried to select a range of cells using the mouse, only to have the cells scroll by so quickly you can't make the ...

Discover More

Selecting a Graphic Behind a Text Box

How to select a graphic that is obscured by a text box can be perplexing. Here's an overview of the different ways you ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Generating a List of Macros

Got a workbook that has lots and lots of macros associated with it? Here's a way you can get a list of all of those ...

Discover More

Running a Macro in a Number of Workbooks

Got a macro that you need to run on each of a number of workbooks? Excel provides a number of ways to go about this task, ...

Discover More

DOS from Macros

Need to run a DOS command from within one of your macros? The answer is the Shell command, described in this tip.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 0?

2023-02-08 09:59:03

J. Woolley

@Ken Kast
See "What About Excel for the Mac?" at https://excelribbon.tips.net/faq.html


2023-02-07 18:14:58

Ken Kast

Again, a tip that is only applicable to Excel on Windows. This is unavailable on the Mac. I ask that a notation be made in the “applies to” section signifying whether a tip is relevant to us Mac users. Sometimes it’s clear, since the tip explicitly references Windows functionality. In other cases, like this one, only stumbling around in Excel preferences and further research on the internet shows that this is a Windows-only solution. Thanks.


2020-05-26 12:49:46

KGF

This worked for me! Thanks for the help.


2020-04-13 12:06:30

Allen

Philip, I'm not sure what you are referring to. There are no "numbered instructions" for this tip, and if I do a search on the page for "Window Color," that phrase appears only in your comment.

-Allen


2020-04-13 11:41:33

Philip Andrew

In the first list of numbered instructions here, it says in item 5 ... "Windows displays the Window Color and Appearance dialog box. (See Figure 1.)"
But the dialog box showing's title is "Trust Center" and doesn't appear to correspond to anything in items 6 through 10. Its now 2-1/2 years since yout last update on this Tip and I'm in Windows 10, XL 365. Are you aware of anything new on Active Cell border color and thickness options/custonization? On a large complex sheet, having the Active Cell "SHOUT OUT" its location would be a Godsend. All best and hoping you're keeping well, we need you!


2019-01-30 12:06:04

Rick

The instructions for Windows 7 *don't work*. Following them merely wasted my time. At work, where I need to get projects done in a timely fashion.

Excel 2016


2018-04-30 08:15:28

Em

Instructions have no do not work selections are still grey (see Figure 1 below) - effects the rest of the PC selections though (see Figure 2 below) . Anyone know of a solution that works for excel?


Figure 1. grey in excel


Figure 2. not grey out of excel




2018-04-22 23:34:00

Chuck Trese

Hi John,
When you click "Add new location" button,....... in the resulting dialog window, .......
there is an optional checkbox for "Subfolders of this location are also trusted"


2018-04-21 10:31:46

John

When adding a new trusted location does it cascade down or do you need to also add each sub-folder?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.