This article is about time saving, when it comes to enable your macros in Excel every time you open the file.

You do not want to allow all macro to run.  But if you don’t, you have to constantly enable your macros over and over again, when you open your Excel files.  To eliminate this, you can create a self-signed digital certificate and this work fine on one PC.

But what, if you need to open the same files with macros from several PC(s) on your network.  I think I found a solution on how to create a certificate on one PC and then install that certificate on other PC(s).

This example tested on Windows 7 and Windows XP machines.

‘

Start with Office 2007 PC

First you need to create a personal certificate (SELFCERT.EXE in Office PROG directory). Run Digital Certificate for VBA Projects from Start Menu\Programs\Microsoft Office\Microsoft Office Tools.  Type a name. Name it as your server, or as your Workgroup. Something that will make scene on all PC(s) where you need to open your Excel files with macros.

Next, open Excel 2007, open your file with a macro that requires a digital signature. You will see the Security Warning.  Open it by clicking on Options… button.  Click on Open the Trust Center link.

In Trust Center select Select Macro Settings and pick option “Disable all macros except digitally signed macros“.

Next open VBA (Alt+F11) and go to Tools –> Digital Signature –>Choose.  Pick the created earlier certificate and OK out of there.

Now you need to exit Excel to save new digital signature that you just assigned, and re-open Excel.

Open the Security Warning one more time and this time pick an option Trust all documents from this publisher. Restart Excel one more time.  This time we are testing to see, if security warning is gone, and you can use your macros right away (without any additional clicks).

‘

Trust from Other 2007 Machines

Now let’s open the same Excel file on other machine with Office 2007.  If you are using off-line file feature, make sure your all synchronized before proceeding further.

Here we need to install the certificate created on first PC and also change macro security settings to only allow digitally signed macros.

A Security Warning that “macros have been disabled” is still there.  We are trying to avoid seeing this warning to save time.

To install a Digital Certificate for VBA in Office 2007, click on Security Warning Options… button.  Click on Show Signature Details –> View Certificate –> Install Certificate….

The crucial moment here is to force the location of the certificate to folder “Trusted Root Certification Authorities“.  If you pick Auto-select option, Windows 7 will  place the certificate in “Intermediate Certification Authorities”, and that will not work.  On Windows XP auto-select option works fine.

Restart Excel one more time, pick option Trust all documents from this publisher from Security Warning dialog box and test if Security Warning is gone on Excel restart.

Trust from PC(s) with Office 2003

If you try to open the same Excel file with Digitally Signed macros in Office 2003, it will be handled very gracefully:

A security warning dialog box will pop up, where you could choose to enable or disable macros.  You do not need to install the certificate, but in that case Excel will continue to prompt your with this dialog box every time you open the file.

The option to actually install the certificate is burred  3 layers deep.  To install the certificate you need to click Details — View Certificate — Install Certificate (see picture above).

`

Additional Related Discoveries

Deleting a Certificate
To delete a personal certificate run certmgr.msc.  Navigate to a desired folder and delete the old certificates.

Adding a Trusted Location
If your PERSONAL.XLSB is located on the network for all machines to share, you will get one more warning dialog box every time you open Excel.  To avoid that, you can use Trust Center to add the folder with your PERSONAL.XLSB to a Trusted Location list.

This is also an option for your regular Excel data directory, but that opens the security gates too wide.  You do not want to trust every macro in your data directory.  That is dangerous.