Automatically Enabling Macros for Specific Workbooks

by Allen Wyatt
(last updated April 21, 2018)

7

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 Office 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

Changing Excel's Background Color

Excel doesn't provide a built-in means to change the standard background color of a worksheet. There are a few different ...

Discover More

Editing a Comment Close to Its Cell

Have you ever chosen to edit a comment, only to find that the comment is quite a ways from the cell with which it is ...

Discover More

Finding Text in Text Boxes

Want to search for text that may appear in a text box rather than in a regular worksheet cell? You can only perform the ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Understanding Subroutines

When developing macros, you can create subroutines. This is a great way to reuse common code and make your programming ...

Discover More

Displaying the Selected Cell's Address

Need to know the address of the cell that is currently selected? The function and macro highlighted in this tip will come ...

Discover More

Telling which Worksheets are Selected

If your macro processes information on a number of worksheets, chances are good that you need your macro to figure out ...

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}] 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 3 + 6?

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.