Re: Excel 2003 VBA: Workbook_SheetCalculate problem.

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 Work***
Set ws = Sh
Dim oServices As New clsServices

' Only continue if change occurred on API ***
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

.

Quantcast