Digitally signing macros

Joe4

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
72,265
Office Version
  1. 365
Platform
  1. Windows
Conversation with Greg Truby, posted for the benefit of others (and with his permission):
Greg,

I have been searching this forum regarding digitally signing macros. I see that it looks like you are using them in an office setting.

I write a lot of macros that we distribute around the office and the "Enable Macros" prompts confuses and/or annoys many users.

I see that to get an "Authentic Certification" requires effort and money, but it appears from your e-mails that you are successfully using the SelfCert. I have a few questions regarding this:

1. Can you use this in an office setting? It appeared to me that it would only work for the signer on his computer. Does that mean everyone would need to sign and save a copy of the file for themselves?

2. In one of the posts it mentions that it self-signing might not work if the macro creates or deletes files. Is that true? Many of my macros create new worksheets.
Click to expand...
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Greg's response:
If you type self certification into the help in Excel 2002 (regular not the VBE) it should return an option for "Create your own digital certificate" which tells you all about it.

To answer your questions:
Yep, I use it all the time. I've written add-ins for a few departments as well as numerous spreadsheets that contain macros and yep, Enabling macros grows tiresome and you don't want them dropping their security to Low. And yep again, getting an "official" certification appears to be pricey - I don't recall the specifics, but I remember when I checked out Verisign I didn't think I'd go that route unless forced to.

Once you get the digital self-certificate installed in your machine then in the VBE just Tools | Digital Signature... and follow your nose.

To get other people to be able to run macros in workbooks that I've signed:
1. They have to drop Security to Medium.
2. The have to open a workbook that has macros + my digital signature.
3. Somewhere in the Enable Macros dialog box it'll say that this is digitally signed and have some button about viewing the certificate or something.
4. They view the certificate which will open up a 2nd dialog box.
5. On that second dialog box there's an option to install it. They install it (accepting the defaults).
6. Back out clicking the "OK"-type buttons.
7. Back at the Enable Macros dialog box (I think) there is a checkbox to "Always trust macros from this source" which for reasons I can't fathom remains disabled.
8. They close the workbook, and then open it again.
9. This time the checkbox for "Always trust..." should be enabled, they check it and now they're good to go for any future workbooks that carry my digital signature.

As far as creating or deleting files, most of my macros don't do a lot of that. But I don't recall ever running into a roadblock. What will remain a roadblock is when the macro wants to do something to the VB Project. In a couple of the add-ins I verify that all of the references are okay which requires for them to have the "Trust Access to the Visual Basic Project" enabled on the 2nd tab (Trusted Sources) of the macro security dialog box. Once they check that, again good for any future activities along those lines. Which may or may not be a good thing - personally I think that this switch should be tied to each Trusted Source (just my 2¢ worth).

The only other consideration that occurs to me is that if your macro hits a bug (not that this ever happens to me ), you can't really patch the bug there at the user's computer. You have to go back to your computer to make any changes. If you try to make changes on their machine, it will void the digital signature (which, really, is what you'd want it to do). So if they have a lot of unique data in that workbook it's a pain in the butt, 'cause they have to e-mail it back to you (or use a network drive). You fix the bug in your original copy plus theirs and so forth. Luckily I haven't had a case where I've had a widely distributed workbook hit this situation. With Add-Ins you can just make the fix, stick it on a network drive and have the users delete the old add-in and install the new one.

Hope this helps.

Greg
Click to expand...
 
Upvote 0
in an office envirement it works great!

just create a certificate with selfcert
add it to your macro's
tell your collegue's to "allways accept macro's from this source"

and all is done!
 
Upvote 0
Quick follow-up:

I tried this out, and it works awesome! And it was so easy to do.

Thanks, Greg!
 
Upvote 0
I tend to refer people w/ questions regarding digital certificates to this thread. And a couple of months ago I began bumping my head :)banghead:) on something new. So I thought I'd jot a note here, where there's a decent chance it might help someone. ;)

If a workbook is shared and AutoRecover is on, this appears to void off the digital signature. My solution to this was to try to disable AutoRecover. Note that EnableAutoRecover is not part of earlier object models. So you have to test for version. Alas the difference in the object models does not correspond to the VBA6 compiling constant, so we cannot use conditional compiling to avoid the compilation error this generates. The solution is to use late binding against generic object variables instead. Here is some code from a workbook that forces macros to be enabled and checks to turn off autorecover to keep this from voiding the DigiSig.
Code: 
Private Sub Workbook_Open()
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
    Dim oMe As Object

    If Val(Application.Version) >= 10 Then      '// EnableAutoRecover not part of object model
        Set oMe = Me                            '// until XL2002. But AutoRecover can void off
        oMe.EnableAutoRecover = False           '// digital signatures.  Use of late binding avoids
    End If                                      '// a compile-time error for versions <= XL2000.

    UnHideSheets                                '// located in module basxl_ForceMacrosAtOpen
    m_booClosingTime = False
    Me.Saved = True
'    CheckEventStatus                            '// Begin making sure Events are always Enabled
End Sub
Again - I have only experienced this in Shared workbooks that autorecover. I have not seen DigiSigs getting voided like this elsewhere.
 
Upvote 0
SELF CERTIFICATE ISSUE

I found your post and used it and it works great.

i no longer have to enable macros.

I tried it on another computer. I saved the excel file to a network folder and then copied it onto another user on citrix.

when i opened I did not have to enable the macros to open the files.

but when I went to use the doc the macros were disabled. the doc never gave me the option to save my digital signiture to their computer.

am i missing a step.

thanks
 
Upvote 0
Check the Macro Security Level on that computer. Is it set to High? If it is, change it to Medium first.
 
Upvote 0
I am currently using this doc on everyone's computer and they all have the macro security level set to medium.

is there a way to manually load my digital signature on their computer?
 
Upvote 0
I'm not familiar with Citrix, so Joe may be correct and it may be getting in the way?

As far as copying a digital certificate. I have never found a way to port my self-cert. Indeed whenever I upgrade my hardware, I end up creating a new digicert. But normally the digital certificate should be part and parcel of a signed project.<sup>1</sup> If you open the project and do Tools | Digital Signature... does the project still contain your sig?

<hr />
<sup>1</sup>As I note above, if you open a signed project on anyone else's computer and you make any sort of change, Excel will void the signature. However, the VBE tells you this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
How to digitally sign an Excel workbook via VBA
Replies
5
Views
3K
becci.gott
B
Kyle123
  • Question
Pending MOTW changes - digitally signing VBA projects
2
Replies
13
Views
1K
becci.gott
B
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
399
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,215,028
Messages
6,122,749
Members
449,094
Latest member
dsharae57

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top