How to Edit Macros in Excel's Visual Basic Editor
Save Custom Functions from Excel VBA in Add-in Files
How to Find Form Controls for Excel Dashboards and Reports

Enable and Trust Macros for Your Excel Dashboards and Reports

With the release of Office 2007, Microsoft introduced significant changes to its Office security model. One of the most significant changes affecting Excel dashboards and reports is the concept of trusted documents. Without getting into the technical minutia, a trusted document is essentially a workbook you have deemed safe by enabling macros.

Macro-enabled file extensions

It’s important to note that Microsoft has created a separate file extension for workbooks that contain macros.

Excel 2007, 2010, and 2013 workbooks have the standard file extension .xlsx. Files with the xlsx extension cannot contain macros. If your workbook contains macros and you then save that workbook as an .xlsx file, your macros are removed automatically. Of course, Excel warns you that macro content will be disabled when saving a workbook with macros as an .xlsx file.

If you want to retain the macros, you must save your file as an Excel Macro-Enabled Workbook. This gives your file an .xlsm extension. All workbooks with an .xlsx file extension are automatically known to be safe, whereas you can recognize .xlsm files as a potential threat.

Enabling macro content

If you open a workbook that contains macros in Excel 2013, you’ll get a message in the form of a yellow bar under the Ribbon stating that Macros (active content) have, in effect, been disabled.

If you click Enable, it automatically becomes a trusted document. This means you will no longer be prompted to enable the content as long as you open that file on your computer. If you told Excel that you trust a particular workbook by enabling macros, it’s highly likely that you will enable macros each time you open it.

Thus, Excel remembers that you’ve enabled macros before and inhibits any further messages about macros for that workbook.

This is great news for you and your clients. After enabling your macros just one time, they won’t be annoyed at the constant messages about macros, and you won’t have to worry that your macro-enabled dashboard will fall flat because macros have been disabled.

Setting up trusted locations

If the thought of any macro message coming up (even one time) unnerves you, you can set up a trusted location for your files. A trusted location is a directory deemed a safe zone where only trusted workbooks are placed. A trusted location allows you and your clients to run a macro-enabled workbook with no security restrictions as long as the workbook is in that location.

To set up a trusted location, follow these steps:

  1. Select the Macro Security button on the Developer tab.

  2. Click the Trusted Locations button.

    This opens the Trusted Locations menu shown in this figure. Here you see all the directories that Excel considers trusted.

  3. Click the Add New Location button.

  4. Click Browse to find and specify the directory that will be considered a trusted location.

    After you specify a trusted location, any Excel file that’s opened from this location will have macros automatically enabled. Have your clients specify a trusted location and use your Excel files from there.

blog comments powered by Disqus
Where Is the Visual Basic Editor in Excel 2013?
Common VBA Statements for Excel VBA Programming
How to Add a Control to Excel Dashboards and Reports
Excel Dashboards: How to Record Your First Macro
Use the Combo Box Control for Your Excel Dashboards