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