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


--
__________________________________
HTH

Bob

"Luke Stubbs" wrote in message
...
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 Flanagan" wrote in message
...
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" wrote in message
...
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?