Problem: A reader posed the following question: “I regularly get files from other people in my company. They arrive via Outlook or are download them from an Internet site. I worry that people aren’t smart enough to avoid getting viruses or could maliciously slip something bad into the Workbook_Open macro to cause problems with my computer.
In Excel 2003, if you opened a file with a macro, it asked you to choose whether to enable or disable macros. How the heck should I know whether I should enable the macros when I haven’t even had a chance to look around the worksheet, or examine the macro code? What do I do?”
Solution: When you answered Enable Macros in Excel 2003, you were really taking a risk. Now is the time to use Protected Mode. Any file that comes from a potentially dangerous location is open in the new Protected mode in Excel 2010. Here is the cool thing about Protected mode: You can look at the workbook. You can scroll through it or go to other worksheets. You can look at the macros. When you are convinced that the file is safe, you click a button and the workbook is available in regular mode.
This is brilliant. You get to actually look at the workbook, and while doing so it cannot harm your computer. You get to make an educated decision as to whether the workbook may prove harmful. And, you know what? A lot of the time, you won’t even have to leave Protected mode. You can look at the worksheet, see what you need to see, and close the workbook.
Which Workbooks Open in Protected Mode?
Basically, any file that did not originate on your computer can open in protected mode. Specifically, these workbooks can be opened in Protected mode:
- Files that you download from the Internet
- Files in your temporary Internet folder
- Files that you open from Outlook
- Files that fail validation
If you want to adjust those settings, click the words in the information bar in Figure 1, and then choose Protected View Settings.
Fig. 1
You can turn off Protected mode for one or more of the situations shown in Figure 2.
Fig. 2
New Rules for Workbooks with Macros and Links
If you frequently use workbooks that have macros or external links, you will like the following improvements.
Normally, if you open a workbook that has macros, you must click the Enable button to use the macros (Figure 3).
Fig. 3
In Excel 2010, when you decide to enable macros for a workbook stored on a local drive, that workbook becomes a trusted document. When you open that same file again, you will not be asked to enable the macros.
This should allow you to keep the setting for Disable All Macros with Notification as shown in Figure 4. In Excel 2003, this setting was known as Medium Macro Security. It forces you to choose to enable macros once for each file stored on a local hard drive.
Fig. 4
In a similar fashion, files with links to external workbooks have a new behavior starting with Excel 2007. Instead of being forced to answer the question of whether you want to update external links, the external links are automatically disabled until someone clicks Enable Content (Figure 5).
Fig. 5
In Excel 2010, when you choose to Enable Content the first time for a file stored on a local hard drive, you will no longer be asked to enable content. You will instead be taken directly to a dialog like the Excel 2003 dialog where you get to choose to enable links or not (Figure 6).
Fig. 6
You can control the startup prompt for one specific workbook by choosing Data, Edit Links to display the Edit Links dialog. In the lower-left corner, choose Startup Prompt. The Startup Prompt dialog shows the choices (Figure 7).
Fig. 7
Untrusting a Document
If you decide to enable content on one workbook, and then discover that the workbook is doing something detrimental, you don’t want those macros or links to automatically work the next time you open the workbook. You can’t change the trusted status for one workbook, but you can reset the trusted status for all workbooks. Choose File, Options, Trust Center, Trust Center Settings, Trusted Documents, Clear (Figure 8).
Fig. 8
Using Trusted Locations
If you want to trust your own workbooks but not trust the workbooks created by others, you can start saving safe workbooks in one specific folder and set up that folder as a trusted location. In the Trust Center, use the Trusted Locations item in the left navigation to set up trusted locations.
Beware of Group Policy Settings Set by Your IT Department
If your employer licenses Office 2010, the people in IT are allowed to set up certain defaults in Group Policy. These defaults can be a bit dangerous in the hands of someone who doesn’t know Excel.
Overzealous IT people might choose to never show you the message that external links have been blocked. This allows the completely insane situation where you think that you have correct calculations in the workbook but you are not getting the correct numbers from external links (Figure 9). If your IT people want the company to restate numbers to the Securities and Exchange Commission, this is a fine choice. Otherwise, they should not force this setting through Group Policy.
Fig. 9
The IT department can force this insane option on everyone in the company. Another setting that you can control through Group Policy is to force certain file extensions to always open in Protected mode (Figure 10).
Fig. 10
To check this setting, use the File Block Settings tab in the Trust Center. You or the IT person who is responsible for setting up Group Policy can control which file types open in Protected mode.
CFO contributor Bill Jelen is an Excel MVP and the author of 35 books about Microsoft Excel. He is also editor-at-large of the CFO Excel Pro newsletter, where this article first appeared.