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

Spacing After Sentences

Word can check to see if you have a consistent number of spaces at the end of your sentences.

Discover More

Minimizing the Ribbon for a Document

Want the ribbon to be minimized for a particular document? Word may not allow you to get the exact result you want, as ...

Discover More

Printing a Portion of a Worksheet

Need to print a portion of a worksheet, but don't want to waste paper by printing the whole thing? It's easy to print ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Finding the Path to the Desktop

Figuring out where Windows places certain items (such as the user's desktop) can be a bit frustrating. Fortunately, there ...

Discover More

Understanding Variables in VBA Macros

You can create and use all sorts of variables in your macros. This tip examines all the different data types you can specify.

Discover More

Progression Indicator in a Macro

When your macro is humming along, minding its own business, a user watching the screen may not see any activity and ...

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 four less than 5?

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.