Handling a worksheet calculate event in XLA code

[TimDB]

Hi

I have code in a VBA project within an XLA (an Excel add-in written in
Visual Basic for Applications) to be triggered by the Calculate event, and it
works as desired (like when F9 is pressed):


' In Sheet1 within the XLA
Public Sub WorkSheet_Calculate()
' Code here
End Sub

Similarly this works too:

' In ThisWorkbook within the XLA
Public Sub Workbook_SheetCalculate(ByVal Sh As Object)
' Code here
End Sub


The above code works because the "hidden" workbook that forms the XLA
receives the Calculate event (as caused by F9), as do all open workbooks.


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 <shift>F9 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?

Many thanks
Tim

 

[Dick Kusleika]

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 <shift>F9 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/archives/2004/06/15/classes-application-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.

 

[TimDB]

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 (<shift>F9) 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 = <shift>F9" Then 'pseudo-code!
'do stuff
Else If
'do other stuff
End If
End If

End Sub

Thansk
Tim

 

[TimDB]

Sorry, calculation_mode wasn't the best choice of words, since
CalculationMode is a real property that refers to automatic or manual
recalculations. Changed to fictitous property "calculation_type" to avoid
confusion....

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

 

[Dick Kusleika]

Sorry, calculation_mode wasn't the best choice of words, since
CalculationMode is a real property that refers to automatic or manual
recalculations. Changed to fictitous property "calculation_type" to avoid
confusion....

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

I don't know of any way to tell how the calc was initiated.