Changing to next race in VBA

[alanh99]

Hi Gary and/or anyone who can help,

I have set up some VBA which monitors prices and amounts bet up to the time a race goes In Play or Suspended. At this point I want to change to the next race in my Quick Pick list. I therefore get VBA to put -1 in Q2. To my frustration, even though on next click, if Q2 is -1, I change Q2 to 5 (as in seconds refresh) BA always jumps two races, ie reads the -1 twice. What to do please?

[GaryRussell]

The Worksheet_Change event is fired multiple times on each refresh. You should make sure your code only runs in the refresh that updates the odds. Also prevent the worksheet event being fired by inserting -1 by disabling events while you change it.

example code

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        [Q2] = -1
        Application.EnableEvents = True
    End If
End Sub


[alanh99]

Thanks for the rapid reply. I think I get the idea. I will try it out and hopefully, all is solved.

cheers, Alan H

[Raiden]

Hey, I have a similar problem:

I've written this code to skip markets after the race is over

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
    If Tabelle1.Cells(2, 5) = "In Play" And Tabelle1.Cells(2, 6) = "Suspended" Then
        Tabelle1.Cells(2, 17) = -1
    End If
    Application.EnableEvents = True
    End If
End Sub

Now BA skips 2 markets, when the race is over.
Is there any way to avoid that?

Thanks

[MarkRussell]

Hi,

Have a look at the code in the following example which will solve your problem:-

http://www.gruss-software.co.uk/forum/viewtopic.php?t=5163

Regards,
Mark