Worksheet Change Event Not Firing / Worksheet Calculate Repetitive Firing

[huyza_0100]

Good afternoon, All!

I have a worksheet that utilizes SAP Office for Analysis. The add-in has a refresh button that refreshes the data source connected and updates the crosstab accordingly. I have a macro adjacent to the crosstab which should fire on worksheet change but it's not firing since Excel doesn't detect the changes. I've tested on worksheet calculate and it does fire it but it my sub gets called indefinitely. I would like to avoid creating a refresh button for users to fire my sub, but I'd like your inputs before I do. The two columns that should fire the sub would be column L or G.

Please Login or Register  to view this content.

[TMS]

In a Change Event handler, Target is a range provided to you by the event handler ... the range that has been changed.

That's not what's happening in your Calculate event. You define/Dim a Target Range and set it it to Range("L:L") and then compare that Target Range to Range("L:L") using Intersect. Well, it is going to Intersect because you’ve just set it. You then call a routine that makes changes in the workbook, probably causing a recalculation and making your "always true" comparison. Hence the loop.

How does SAP provide updates to the worksheet? A Change Event handler will not fire if the range/cell(s) being monitored contains a formula/formulae. Your change event handler is only monitoring column G, not L and G.

[huyza_0100]

It provides just a flat table with no formulas. There's a button that does a callback to SAP service: Application.Run("SAPExecuteCommand", "Refresh", "DS_1")

The interesting thing is that when the table expands or contracts my actual rows expand and contract too but it doesn't trigger the worksheet change/calculate event.