Digital signature breaks on insertion of new Worksheets

[Luke Stubbs]

I have created a spreadsheet containing VBA code that I want to distribute
to customers. In order to let me run VBA on clients' machines, I have bought
a digital signature and have signed my file.

My problem is that the code needs to create new worksheets, but if I save
the file after doing so, Excel complains and drops the signature with the
error message "You have modified a signed project. You do not have the
correct key to sign this project. The signature will be discarded."

I think this arises because the new worksheet generates a corresponding new
'Microsoft Excel Object' - i.e. Sheetx, and that VBA then considers this
amounts to changing the macros and therefore requires them to be re-signed.
My question - is this correct and if so, are there any workarounds?

Thanks

Luke



Background information

- version : Excel 2003. Upgrading to 2007 is a possibility if it overcomes
the problem.

Other questions

Having read around the problem, I seem to have two options:- force users to
run macro security at low, or modify my code to run as an Excel Addin file.
I've never had to do either before, so

- Can I set a user's macro security level programmatically?
- If I went for the add-in approach, what would I lose? My workbook is
currently very interactive, with Macros running on some Change and Select
events; would that have to go?

 

[Bob Flanagan]

If the workbook is modified and saved on the client's machine, then there is
not way to digitally sign with your signature, as your signature key files
are on your machine, many miles away. If the client needs all his files
digitally signed, then they need to sign the workbook with their signature.
I suspect they are not digitally signing their workbooks.

What you may want to do is split up your macros and your workbook. Put all
your macros into an add-in workbook and digitally sign it. Do not put any
macros in the worksheet workbook. Thus it will not need to be signed. It
will also make updating the macros easier as you only need to change the
add-in file.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Luke Stubbs]

Perhaps that's what I'll have to do; it would be a pity though because I'd
have to throw away some useful code that fires on cell change. The purpose
of my spreadsheet is to allow bets on football matches to be created easily.
When one of the odds changes, it ripples through and updates other odds -
but this depends on using an OnChange event. I suppose I could just drop
that and use a refresh button, but it wouldn't be the same.

Strangely it seems that I can insert blank sheets without ill effect and I
can even copy and paste data onto those blank sheets, but I can't add code.
Another hideous alternative would be to create a pool of a couple of hundred
hidden sheets and when a new sheet is needed, just assign one of those.
Would that work?

Thanks

Luke

 

[Bob Phillips]

Luke,

You can still have code that fires on a change event in another workbook,
you would just use application events rather than worksheet events.

This is an example of such

Option Explicit

Private WithEvents xlApp As Application

Private Sub Workbook_Open()

Set xlApp = Application

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Name <> "League Results" Then Exit Sub

If Not Intersect(Target, Sh.Range("A1:A10")) Is Nothing Then

'do your stuff being careful about ranges and sheets
End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

 

[microsoft excel]

how do i go about to sign my files?

someone pointed me toward a verisign site i think but i could not figure out
everyting...

thanx

 

[Bob Phillips]

Verisign is expensive, and signing causes lots of problems. Most
professional developers that I know don't bother with signing.

 

[microsoft excel]

thanx bob.


But how do i go about when i send a file to another user/computer? it
always ask if they trust the file. the major part of ppl using it is born
BC (before computers) and to have them set it every time to not ask if they
trust the macro is a hazzel. i did sign it with the office thing. but then
they need to set it to not ask it. when i send a "update" it needs to be
set again...

 

[microsoft excel]

thanx bob.


But how do i go about when i send a file to another user/computer? it
always ask if they trust the file. the major part of ppl using it is born
BC (before computers) and to have them set it every time to not ask if they
trust the macro is a hazzel. i did sign it with the office thing. but then
they need to set it to not ask it. when i send a "update" it needs to be
set again...

 

[Luke Stubbs]

For what it's worth, we went with Comodo . Signing the code is important as
the company I'm working for has some hundreds of installations, many abroad
and we're going to encrypt the final Excel document, so they can't self
certify.

Regards

Luke