Automatically Enabling Macros for Specific Workbooks

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.