Workbook_SheetCalculate Events

[Tom]

Hi,

I am currently using Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
to execute code when the value of any cells (provided by external links)
change.

Is there any way to filter this so my code only runs if cells *within a
particular range* change? Something like :

if Sh.Range = within the range A1 to C10 then run the code
else do nothing

Seems a simple enough concept but I can't for the life of me figure it out!
I have to use "calculate" events rather than "change" events as the cells
are updated automatically, rather than by a user.

Thanks,

Tom

 

[Jim Cone]

Tom,
Try this code in the worksheet module not the workbook module.
The code is only practical on a small range as the time required to
run it increases greatly as the range size increases.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Option Explicit
Private varValue As Variant

Private Sub Worksheet_Calculate()
Dim lngR As Long
Dim lngC As Long

If Not IsEmpty(varValue) Then
For lngC = 1 To 3
For lngR = 1 To 10
If Me.Range("A1:C10")(lngR, lngC) <> varValue(lngR, lngC) Then
'run your code instead of showing msgbox
MsgBox "ran code"
varValue = Me.Range("A1:C10").Value
Exit Sub
End If
Next 'lngR
Next 'lngC
Else
'run your code instead of showing msgbox
MsgBox "ran code_"
varValue = Me.Range("A1:C10").Value
End If
End Sub
'-------------


"Tom" <[email protected]>
wrote in message
Hi,
I am currently using Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
to execute code when the value of any cells (provided by external links)
change.

Is there any way to filter this so my code only runs if cells *within a
particular range* change? Something like :

if Sh.Range = within the range A1 to C10 then run the code
else do nothing

Seems a simple enough concept but I can't for the life of me figure it out!
I have to use "calculate" events rather than "change" events as the cells
are updated automatically, rather than by a user.

Thanks,

Tom

 

[Tom]

Hmm looks interesting, thing is speed is absolutely of the essence for what
I'm doing. The whole code gets run several times a second and it needs to
be as responsive as possible. Which is why I'm trying to make it more
selective as to which cells trigger it to run, so it doesn't get run more
than absolutely necessary. The code also updates some cells, which in turn
could trigger it to be run again, or should application.enableevents=false
stop that?

Any other ideas most appreciated!

 

[Jim Cone]

Did you try the code?
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"Tom" <[email protected]>
wrote in message
Hmm looks interesting, thing is speed is absolutely of the essence for what
I'm doing. The whole code gets run several times a second and it needs to
be as responsive as possible. Which is why I'm trying to make it more
selective as to which cells trigger it to run, so it doesn't get run more
than absolutely necessary. The code also updates some cells, which in turn
could trigger it to be run again, or should application.enableevents=false
stop that?

Any other ideas most appreciated!