Macro Security - Digital Certificates

[yawnzzzz]

I'm wanting to distribute an excel file containing VBA to many different people, and I'm hoping to avoid explaining how to change their macro security settings. It's going to differ by user on their version of Excel and what level of security is set, so I'm looking for the best approach to just make it work with zero or the least amount of user knowledge.

For those on High Security, it appears that a digital certificate might be the best approach. Verisign seems to be the number one choice, but CACert offers a free certificate as well. I have a few questions:

1) If I have a trusted digital signature on my workbook, will it still prompt the user notifying them that it's signed or will it just run?

2) Is Verisign trusted by Microsoft Office? Or is there any advantage over using Verisign instead of CACert?

3) I'm going to have the VBA locked down, so users can't touch it, but if a user were to enter information in a spreadsheet and save the workbook, would the signature become invalid when they open it again?

[elprup]

You could have a sheet with a message that says they must enable macros in order to continue. Once they enable macros, then that sheet will get hidden and the sheet you want them to see would become unhidden. This way it wouldn't matter what settings they had. You're essentially forcing them to enable macros in order to use your file.

[elprup]

Hmmm, I can't find my code where I did this sort of thing in the past. I believe this wasn't too hard to find on the internet. I remember when I did this I took what I found and then modified it a bit.

You have to use workbook open and close events. I'll keep looking, but someone else will probably be able to fully help you sooner than me finding my file. I apologize for only providing partial help.

[elprup]

Found it (which makes me feel better )

Your stuff is on sheet1. Now, create a new sheet and put a simple message on it saying they must enable macros to continue. Hide sheet1. In the VB viewer window, double-click ThisWorkbook and copy+paste the code below into that window. Save your file as a macro enabled workbook and you should be ready to roll.

Private Sub Workbook_Open()

    Sheets(1).Visible = True
    
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Sheets(1).Visible = xlSheetVeryHidden

    
End Sub

...actually, this wasn't the part I modified since these are only single lines.

[royUK]

Which version of Excel will be used?

The Digital Signature on your computer will not apply to other PCs

A digital signature becomes invalid when the VBA Project is altered by others,if the project is secured then this will reduce the chance of this happening.

[shg]

Users will be advised that you have a VeriSign certificate, and then asked (a), if they want to allow the code to run, and (b), if they always want to trust you as a publisher. If they do, they will not be prompted in the future.

[yawnzzzz]

Thanks for all the responses.

Elprup, I'll consider that as a back-up plan.

Shg, do you know if users will receive the exact same message for a CACert? I'd rather go with CACert since they're free, but I'm wondering what the differences might be. For instance, I'm guessing most firewalls allow verisign now, but might not allow cacert.

Roy, I'm creating it in Excel 2007 and saving in 97-2003 compatibility mode. The users could be using any version of Excel. By your statement, do you mean that a Digital Signature for the VBA would still remain valid if a user changed spreadsheet values and saved? Do you know any circumstances that could cause a digital signature to become invalid if you have the vba locked (barring a user guessing the password)?

[royUK]

Only changing the VBA Code would affect the signature