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:
Select the Macro Security button on the Developer tab.
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.
Click the Add New Location button.
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.