Thanks very much Dick - the approach you described worked nicely.

A further related question: is it possible to tell what type of calculation
is taking place, namely whether it is just for the sheet (<shiftF9) or all
workbooks (F9)?

Using your example:
Private Sub xlApp_SheetCalculate(ByVal Sh As Object)

If Sh.Parent.Name = "MyBook.xls" And Sh.CodeName =
appXL.ActiveSheet.Name Then
If "calculation_mode = <shiftF9" Then 'pseudo-code!
'do stuff
Else If
'do other stuff
End If
End If

End Sub

Thansk
Tim

"Dick Kusleika" wrote:

On Thu, 19 Feb 2009 13:13:01 -0800, TimDB
wrote:



However, I need to trigger code within the XLA when a Worksheet_Calculate
event occurs in a worksheet that is part of *another* loaded workbook (not
the XLA), for example, due to pressing <shiftF9 when that worksheet

is active. Currently my code fails because only the active worksheet
receives the calculate event, not the XLA workbook or any of its worksheets.
I have to acheive that without manually copy'n'pasting code into the VBA
project of the other loaded workbook.

So my question is:

Can an XLA in Excel 2007 install an event handler for the
worksheet_calculate event for worksheets in an ordinary workbook when that
workbook is loaded into Excel, and if so, how?


Hi Tim: Kind of. You need to create a custom class module in your XLA that
exposes Application Level events.

http://www.dailydoseofexcel.com/arch...cation-events/

Then you can use the SheetCalculate event. That will fire every time *any*
sheet calculates, so you'll need some code to identify that *the* sheet
calculated. It might look like this:

Public WithEvents xlApp As Application

Private Sub xlApp_SheetCalculate(ByVal Sh As Object)

If Sh.Parent.Name = "MyBook.xls" And Sh.CodeName = "Sheet2" Then
'do stuff
End If

End Sub

How you identify your sheet will likely be different.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com