Occasionally, I get calls from clients who don't understand why their Excel file isn't working. They're clicking buttons, or selecting from drop down lists, but none of the usual magic is happening. Is the file broken?
When you open a workbook in Excel 2007, a security warning bar might appear above the worksheet, telling you that macros have been disabled.
Unfortunately, the security bar blends into the Ribbon a little too well, and if you've created the workbook for someone else, they might not even notice the warning.
When I ask my clients with the broken workbooks if they see the warning, usually the answer is yes. A couple of clicks later, the file is working again. It's annoying though, and makes smart people feel not so smart.
Bypass the Security Warning
For Excel workbooks that you know are safe to open, you can bypass the security warning by putting the files into a trusted folder. It'll take you a minute or two to set it up, and that little bit of effort will save you countless hours and aggravation.
To set up a trusted folder:
- Click the Office Button on the Ribbon, then click Excel Options.
- In the list of categories, click Trust Center, then click Trust Center Settings.
- In the Trust Center dialog box, click Trusted Locations.
- You can see the list of current trusted locations, and buttons to Add New Location, Remove or Modify items in the list.
- Click the Add New Location button
- Click Browse, select the folder that you want to trust, then click OK.
- Your folder is now in the list of trusted locations.
- Click OK to close the Trust Center and Excel Options dialog boxes.
Now, when you open the files from that folder, the macros will be automatically enabled. No more missed Security Warning bar, or mysteriously broken Excel files.
No Macros in the Excel Workbook
If the workbook doesn't have macros, and you're seeing a macro warning, you can follow the instructions here: Remove Excel Macros Security Warning.
__________
Thank you, prompted me to finally sort that out.
Modal dialogs are a pain in the neck, but they make you select something. In hindsight, enabling macros is a poor choice for a modeless warning. In response to user complaints, Microsoft has an Improved Macro Security Warning in Excel 2010, which is colored bright red, so it's harder to miss.
There are other ways of doing this
1. Convert the Excel file to a Exe file
2. Open the file via a macro
Thanks Alex! I like to do this for my own files that I open frequently, and for client files where the Security bar might cause confusion.
Sam, thanks for the tips!
I therefore trust that Access warning will also work the same way and the outlined procedures will work the same? Note I did not use the word assume for good reason. In that you don't see that warning in 2003 Excel does it still mean that a trust must be applied to that Excel version also if you are using macros on those sheets?
Fred, the trust center setting seems to work the same in Access 2007 as it does in Excel 2007. It's not available in Excel 2003 or earlier versions though.