Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling a worksheet calculate event in XLA code
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 <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? Many thanks Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling a worksheet calculate event in XLA code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling a worksheet calculate event in XLA code
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling a worksheet calculate event in XLA code
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 = <shiftF9" Then 'pseudo-code! 'do stuff Else If 'do other stuff End If End If "TimDB" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Handling a worksheet calculate event in XLA code
On Wed, 4 Mar 2009 06:17:01 -0800, TimDB
wrote: 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 = <shiftF9" 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. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
Why does this Worksheet Calculate Event run so slow? | Excel Programming | |||
Worksheet-specific event handling needed | Excel Programming | |||
Worksheet Calculate Event | Excel Programming |