EXCEL: Auto-Run Macros Disabled without Warning


This entry applies if your security settings in Excel are set to “Disable all macros except digitally signed macros” like this:

disabledmacros

In this scenario, when you open a workbook that is digitally signed, you will get a yellow bar across the top of the screen, that says: “SECURITY WARNING Macros have been disabled”, like this:

macrowarning

But if the workbook is NOT signed and you attempt to open the workbook with macros, you will not see anything, no warning at all. The workbook will appear to open, but all macros will be disabled. If you try to run a macro, you will get the following warning:

macrowarning2

However, the insidious thing is your AutoRun macros will also not execute and you will get no warning. If your code performs some type of data refresh of the spreadsheet, you might not realize this did not happen and what you will be left with is stale data. For many financial institutions this can be a scary prospect.

To workaround this problem, I have created an XLAM that can warn your users that they opened an untrusted workbook. You can access the source and the add-in both on GitHub: VBAProjectNotSignedWarning-Addin

What this add-in does is look for XLSM file to be opened, it then checks to see if the VBAProjectSigned property is false. If the workbook code is not signed, macros are assumed to be in the file, and the user will get the following warning:

certwarningaddin

The SOURCE is provided in the depot because for it to run properly in your environment, you might want to open the XLSM sign the VBA Project with your corporate code certificate, secure the project with a password, and then Save A Copy as an XLAM (Excel Add-in). Once created, you can then place the add-in in the XLSTART folder in Program Files for each user in your organization. Then when they try to open an unsigned macro workbook, they will get a handy warning message.

The code for this is fairly simple. I will not go into each module, just the main function that performs the check of each Workbook that is opened via the Application.Workbook_Open event:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s