Excel 2003 VBA: Workbook_SheetCalculate problem.

[Jimmah]

Hi,
I have been using the Workbook_SheetChange event to automatically
call a web service on a change in a cell, I have now realised this
event is only fired if a cell is changed manually. I have noticed the
Workbook_SheetCalculate event, but this does not have the "Source As
Range" parameter, which I need.

Is there a way to programmatically work out which range has been
changed during the recalculate?

Any help with this would be much appreciated.

Thanks in advance.

James

 

[Jimmah]

I need the following code to run only if a specific range has been
changed by a formula,

The code I'm using currently is:

Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As
Range)

' Dimension variables
Dim runnerNum As Long
Dim betType, mktID, selID As Long
Dim Price, Size As Double
Dim msg As String

' Dimension objects
Dim ws As Worksheet
Set ws = Sh
Dim oServices As New clsServices

' Only continue if change occurred on API sheet
If ws.Name = "API Interface" And Range("betting").Value = "ENABLED"
Then
' Only continue if change occurred in allowed columns
If Source.Column = 3 Then
' Grab the runner number
runnerNum = CLng(ExtractNumbers(Source.Name.Name))

If Not CStr(Range("runner" & runnerNum &
"BackPrice").Value) = "" And Not CStr(Range("runner" & runnerNum &
"BackSize").Value) = "" Then
' Grab the current details
betType = 0 ' Back Bet
mktID = CLng(Range("mktID").Value)
selID = CLng(Range("runner" & runnerNum & "ID"))
Price = CDbl(Range("runner" & runnerNum & "BackPrice"))
Size = CDbl(Range("runner" & runnerNum & "BackSize"))

' Place the bet
msg = oServices.PlaceBet(betType, mktID, selID, Price,
Size)

' Display the Message
Range("betStatus").Select
ActiveCell.Value = msg

' Refresh Prices
'Call modAPI.RefreshPrices
Else
Exit Sub
End If
ElseIf Source.Column = 5 Then
' Grab the runner number
runnerNum = CLng(ExtractNumbers(Source.Name.Name))

If Not Range("runner" & runnerNum & "LayPrice").Value = ""
And Not Range("runner" & runnerNum & "LaySize").Value = "" Then
' Grab the current details
betType = 1 ' Lay Bet
mktID = CLng(Range("mktID").Value)
selID = CLng(Range("runner" & runnerNum & "ID"))
Price = CDbl(Range("runner" & runnerNum & "LayPrice"))
Size = CDbl(Range("runner" & runnerNum & "LaySize"))

' Place the bet
msg = oServices.PlaceBet(betType, mktID, selID, Price,
Size)

' Display the Message
Range("betStatus").Select
ActiveCell.Value = msg

' Refresh Prices
'Call modAPI.RefreshPrices
Else
Exit Sub
End If
End If
End If
End Sub