Delay calling a Macro

[RINCONPAUL]

I've got this code which won't run, and would appreciate some help in getting it to work. I have placed it in Sheet1 of the VBA editor.

Private MyMarket As String
Private InPlay As Boolean
Private RefreshCount As Long


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Select Case Target.Columns.Count
Case 16 'odds and market info update

'check to see if market changed
If Range("A1").Value = MyMarket Then
RefreshCount = RefreshCount + 1
Else
MyMarket = Range("A1").Value
RefreshCount = 1
InPlay = False
End If

'waits 1 second, calls macro if "In Play"
If RefreshCount > 2 And Range("E2") = "In Play" And Range("F2") <> "Suspended" Then
Application.Wait Now + TimeSerial(0, 0, 1)
Call Copy_Race
End If

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Select
End Sub

[mdmackillop]

A couple of issues
If you're trying to detect a change in column P then you want

Select Case Target.Column
Your code will run if you delete A1:P1 i.e. target is 16 columns


The following will nor reset events if case <>16. Keeep both inside the select


Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Select Case Target.Column
Case 16 'odds and market info update
.....
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Select



Preferably, IMO


Application.EnableEvents = False
Application.Calculation = xlCalculationManual
on error goto exits
Select Case Target.Column
Case 16 'odds and market info update
.....
End Select


Exits:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

[RINCONPAUL]

Thanks so much for your reply md. Changed code to below but now getting Compile Error 'End Select without Select Case'. Another thing though. This Sheet is part of an application. The cells are fed from Betfair and therefore always changing, however they all change in 16 columns when a race changes, hence a signal for a new event. It's a pretty well standard procedure to use this syntax for the program 'BA' advice is:
Yes, any VBA program that cooperates with BA should have the structure:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
...
Application.EnableEvents = True
End Sub

I have amended my code to below, but getting that error I mentioned. I'm thinking it's something else? :banghead:


Private MyMarket As String
Private InPlay As Boolean
Private RefreshCount As Long


Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo Exits
Select Case Target.Column
Case 16 'odds and market info update

'check to see if market changed
If Range("A1").Value = MyMarket Then
RefreshCount = RefreshCount + 1
Else
MyMarket = Range("A1").Value
RefreshCount = 1
InPlay = False
End If

'waits 1 second, calls macro if "In Play"
If RefreshCount > 2 And Range("E2") = "In Play" And Range("F2") <> "Suspended" Then
Application.Wait Now + TimeSerial(0, 0, 1)
Call Copy_Race
End If
End Select
Exits:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Bob Phillips]

You have removed the End If before the End Select.

[RINCONPAUL]

xld
Added the 'End If'....now Run time error 1004...method 'Calculation of objective_Application failed'

[mdmackillop]

Where does the error occur?

[RINCONPAUL]

Sorry md, been asleep. I've rebooted fresh and now there are no errors, just doesn't call the macro?
xld suggested changing to Worksheet_SheetChange

not

Worksheet_Change

No difference?

[mdmackillop]

Check that Application.EnableEvents are enabled.

[RINCONPAUL]

I got this sorted elsewhere. Apparently I was making it too complex:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook
If .Sheets("sheet1").Range("T2").Value <> 1 Then
.Sheets("sheet1").Range("T3").Value = 0
End If
If .Sheets("sheet1").Range("T2").Value = 1 And .Sheets("sheet1").Range("T3").Value <> 1 Then
Application.Wait Now + TimeSerial(0, 0, 1)
Call Copy_Race
.Sheets("sheet1").Range("T3").Value = 1
End If
End With
Application.EnableEvents = True
End Sub

'Change the sheet name to the sheet name where Gruss is active add this formula to cell T2

=IF(E2="In Play",1,0)

It's working almost perfectly. There's a small issue with the delay?

Background: When a race or match goes 'In Play' and a cell is populated with that text, odds that were changing constantly, are replaced with a set of fixed odds (called Starting price). They appear after a second of the 'In Play' showing. Hence the requirement to delay the Call Copy_Race which needs to capture those fixed odds. With the code as is, it is copying the odds showing when 'In Play' appears. The Application.Wait Now is supposed to delay calling the macro Copy_Race. Now all Copy_Race does is copy the sheet1 cells and paste to sheet2. It delays the paste part but not the copy part. Weird!

[SamT]

Insert three new lines where indicated
Application.EnableEvents = True
Do Events
Application.Calculate
Call Copy_Race

[RINCONPAUL]

Sam, I found another work-round. Probably not fair asking you guys to solve something related to an application that you can't access, and to understand the sheets idiosyncrasies.
All the best,
Thanks to all