Automatically Enabling Macros for Specific Workbooks

Written by Allen Wyatt (last updated June 22, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365


1

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

Allowing Only Form Field Changes

Word allows you to create forms that other people can use to enter information. One of the last steps normally taken with ...

Discover More

Using the IRR Function

When working with finances, you often need to know the rate of return on a given investment. The most common type of ...

Discover More

Moving an Entire Page

If you are new to using Word, you may wonder if there is an easy way to move pages around in the document. Word, though, ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Removing All Macros

Macros are stored as part of a workbook so that they are always available when you have the workbook open. If you want to ...

Discover More

Deleting Blank Rows

Got some pesky blank rows in your data that you want to get rid of? This tip provides a wide variety of methods you can ...

Discover More

Continuing Macro Lines

Sometimes a macro command line can get very, very long. This can make it hard to understand when you look at it a month ...

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 9 + 7?

2024-06-22 11:26:05

R. A. Williams

PLEASE STOP THE GOOGLE ADS!!!
In general, the ads you have had on your site have been innocuous, but Google ads are THE WORST! They take more time to try to dismiss, but the space they occupy never goes away! There were at least five of them in this email, mostly duplicates.
I hope your income from these ads compensates for the angry messages you must be getting from your faithful subscribers. Love your tips, hate your Google ads!
Sincerely,
Bob Williams, a long-time subscriber


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.