Trusting Macro-Enabled Workbooks | Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

If you frequently use workbooks that contain macros, having to enable the macro search time you open a workbook can be annoying-so annoying, in fact, that you might be tempted to disable the warning. Please don’t do that. Eliminating the warning dramatically increases your computer's vulnerability to macro viruses. Excel 2007 provides two simple alternatives that allow you to safely eliminate the warning for workbooks that you trust. One alternative involves trusting the location of the macro workbooks. The other alternative involves trusting the creator (or publisher) of the workbook.

Designate a Trusted Location for Macros

The concept of a trusted location is that you designate a folder as a trusted, and then don’t put macro-enabled workbooks into that folder if there is any chance that the workbook might be unsafe. Excel will then open any macro-enabled workbooks stored in that folder without a warning. Setting up the trusted location is a little bit complicated, but you only have to do it when you want to trust a new location.

Close the Chapter01 workbook, and then re-open it.
In the warning bar, click the Options button, and then click the Open the Trust Center link at the bottom of the pop-up.
In the Trust Center dialog box, select the Trusted Locations group, and then click the Add new location button.
In the Microsoft Office Trusted Location dialog box, click the Browse button, navigate to the folder you created for the Chapter01 macro-enabled workbook, and click OK to put the folder name into the Path box.
Click the OK button three times to add the folder and close the dialog boxes.

The warning message should disappear.
Close the workbook, and then re-open it.

It should open without a warning.
Select cells D16:F23 and press Ctrl+Shift+C to format the cells as customized currency.

This confirms that the macro still works.

If you put a workbook in a trusted location, you don’t have to explicitly enable the macros. You have the responsibility to make sure that only trustworthy documents get into any of the trusted locations. Certain folders-such as the Temporary Internet Files folder-can never become trusted locations. You may want to look at the list folders Excel trusts by default and remove some of them as well, just to be safe.

Designate a Trusted Publisher for Macros

Specifying a trusted location is good for those situations where you can store all your macro-enabled workbooks in a very limited number of folders. It is also convenient when you receive macro-enabled workbooks from several different (trustworthy) colleagues. But if you create macro-enabled workbooks and need to store them in arbitrary locations, you can also designate yourself as a trusted publisher.

First, you need a macro-enable workbook that is not in a trusted location.

Click the Microsoft Office Button, point to Save As, and click Macro-Enabled Workbook.
Navigate to an un-trusted folder (such as the main folder that contains the practice files for this book), change the name of the workbook to Chapter01A.xlsm, and then click Save.

In order to trust yourself as a publisher, you must be able to prove who you are. To do that, you must first create a digital ID. The Microsoft Office Button contains a shortcut that allows you to create the digital ID you need.

In Excel, click the Microsoft Office Button, point to Prepare, and click Add a Digital Signature.

Troubleshooting
If you see a message that encourages you to go to the Microsoft Office Marketplace, click OK to go to the Get A Digital ID dialog box.
In the Get a Digital ID dialog box, select Create your own digital ID.

Creating your own digital ID is secure, but it is also valid only for you and on only the current computer. If a different user logs into your computer, the ID will not be valid for them. If you copy the workbook to a different computer, the ID will not be valid there. If you need a digital ID that can be used in multiple environments, you need to obtain one from a trusted source. You can either purchase a digital ID, or check with your company’s Information Technology department to see if they can provide a digital ID for use within the company.
Click OK to display the Create a Digital ID dialog box, and enter your name, plus any additional information you choose.
Click Create, and then stop.

Do not click the Sign button. Do not pass Go. Do not collect $200. Click Cancel.

Important

Adding a digital signature to a workbook is different from adding one to the VBA project stored inside the workbook. The Prepare command on the Microsoft Office Button is a convenient way to create a new digital ID, but if you actually sign the document, you prevent any changes to the workbook cells. Conversely, when you add a signature to a VBA project, only the macros are signed and anyone can still make changes to the workbook. Make sure you go to the Visual Basic editor to add the actual signature.

Switch to the Visual Basic editor, click the Tools menu, and click Digital Signature.

The top portion of the Digital Signature dialog box shows whether the project is currently signed. The bottom portion shows digital signatures that are already trusted.
Click the Choose button, and then in the Select Certificate list, select the certificate you just created-it should have your name on it.
Click OK.
After confirming that the project is currently signed with your certificate, click OK again.

You now have a digital ID, and you have used it to sign the project, but there is still one more step: You have not yet told Excel that you want to trust yourself as a publisher.
Switch back to Excel and close the Chapter01A workbook, saving the changes back to the untrusted location. (The change you made was to sign the VBA project.)
Re-open the workbook, and click Options on the Security Warning bar.

An ominous-looking security alert appears, but all it really says is that “you have not yet chosen to trust” yourself.

Give it a lot of thought, but if you do decide to trust yourself to publish only macros that don’t destroy your own computer, select Trust All Documents From This Publisher, and then click OK.

Troubleshooting

Depending on how you close and open the workbook, a different form of the security alert may appear immediately. Just click the Trust All From This Publisher button in the security alert and continue.

Close the workbook and open it again. No notice appears.
Select a suitable cell that contains a formula, and press Ctrl+Shift+V to make sure the macros work.
To confirm the Trusted Publishers list, click the Microsoft Office Button, and then click Excel Options at the bottom.
Select the Trust Center tab and click the Trust Center Setting button on the right.
Select the Trusted Publishers tab and see your certificate listed.

You can revoke the trust for a publisher at any time, just as you can revoke the trust for a location at any time.

Click Cancel twice to close the dialog boxes.

Important

You can use the Prepare command to create a digital ID, but you cannot use the same approach to delete a digital ID. To delete a digital ID from your computer, you must go to Internet Options in the Windows Control Panel. On the Content tab, click Certificates. The Certificates dialog box allows you to delete or import digital certificates from your computer, but it does not allow you to create a new personal digital ID. Creating a personal digital ID is part of the 2007 Microsoft Office system. If you remove a digital certificate from your computer, that does not remove it from the trusted publishers. You can still use the macro-enabled workbook, but making any subsequent changes to the macros destroys the signature on the project.

Security is important when you create any programs, including macros. Security is especially important when you share applications with others. If you are mostly creating macro-enabled workbooks for your own use, a personal digital ID is a simple, but flexible solution. If you are sharing macro-enabled workbooks with a small group of people, the trusted locations approach is probably a good solution. If you need to create macro-enabled documents for a wider audience, you’ll probably want to obtain a properly authenticated digital signature to allow others to take advantage of your work.

CLOSE the Chapter01.xlsx workbook.