Working With Excel Macro Security
When you open a workbook in Excel, 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.
Trust Centre Settings
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. This would, however, need to be carried out on every Computer/User Profile.
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.
You can obtain a digital certificate from a commercial certificate authority (CA) or from your internal security administrator or information technology (IT) professional.
To learn more about certificate authorities that offer services for Microsoft products, see the list of Microsoft root certificate program members.
Digitally sign a macro project in Excel, PowerPoint, Publisher, Visio, or Word
- Open the file that contains the macro project that you want to sign.
- On the Developer tab, in the Code group, click Visual Basic.
Note: If the Developer tab is not available: Click the File tab. Click Options. Click Customize Ribbon. In the Customize the Ribbon list, click Developer, and then click OK.
- In Visual Basic, on the Tools menu, click Digital Signature.
- The Digital Signature dialog appears.
- Select a certificate and click OK.
- It is recommended that you sign macros only after your solution has been tested and ready for distribution: when code in a signed macro project is changed in any way, its digital signature is removed. However, if you have the valid digital certificate that was previously used to sign the project on your computer, the macro project is automatically re-signed when you save it.
- If you want to prevent users of your solution from accidentally changing your macro project and invalidating your signature, lock the macro project before you sign it. Your digital signature implies that you guarantee the project has not been tampered with since you signed it. Your digital signature does not prove that you wrote the project. Therefore, locking your macro project doesn’t prevent another user from replacing the digital signature with another signature. Corporate administrators can re-sign templates and add-ins so that they can control what users run on their computers.
- If you create an add-in that adds code to a macro project, your code should determine if the project is digitally signed and should notify the users of the consequences of changing a signed project before they continue.
- When you digitally sign macros, it is important to obtain a time stamp so that other users can verify your signature if the certificate used for the signature has expired, or has been revoked after signing. If you sign macros without a time stamp, the signature remains valid during the validity period of your certificate.