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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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?
>>>
>>
>>
>
Similar Threads
