Clear a range of Cells when a Horse Race goes In-Play

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

Hi, I am trying to use vba code to clear a range of cells when a race goes in-play and displays the text "In-Play" in cell G1 (as it does on the BA Multi race sheets).

The code below will only work if I type "In-Play" into cell G1, but does not work when the cell simply displays "In-Play" as it does when a race goes in play.

I am guessing (I'm a beginner at this) that there is a difference in typing in a cell, and what happens when a race goes in play and the cell automatically changes to "In-Play"

Can anyone help .. mucho appreciated
regards
Paul

Code: Select all

Option Explicit
Private WithEvents App As Application
Private Sub Class_Initialize()
    Set App = Application
End Sub
Private Sub App_SheetChange(ByVal Sh As Object, _
                            ByVal Target As Range)
   
    Const Exclude As String = "one/Two/Three"
    Const StopCell As String = "G1"                 
    Const ClearRange As String = "E120:K152"        
    
    If InStr(1, Exclude, Sh.Name, vbTextCompare) = 0 Then
        With Target
            If .Address = Range(StopCell).Address Then
                If StrComp(Trim(.Value), "In-Play", vbTextCompare) = 0 Then
                    App.EnableEvents = False
                    Sh.Range(ClearRange).ClearContents
                    App.EnableEvents = True
                End If
            End If
        End With
    End If
End Sub

nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Right click on the sheet tab and put this in

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("G1") = "In-play" Then

    Application.EnableEvents = False

    Range("E120:K152").ClearContents
        
    Application.EnableEvents = True

    End If

End Sub
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

Thanks for your reply Nigelk .... just about to try it on the wolves 6:50 ..

just thought I'd mention that there is already code there when I right click the sheet as per the folowing :-
Will it be ok to just past your code underneath it ?

Code: Select all

Private Sub Workbook_Open()
    Application.Iteration = True
End Sub
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

That code should only fire when the workbook opens. Don't know what its' doing in there.

If you open the vba editor on the left hand side you should see a list of all the sheets, underneath is 'this workbook'. It should go in there.
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

ok thanks again, seems to do the job great ...
Cheers & thanks very much for your help
Paul
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

Just one other thing, when it fires, the cursor/ pointer starts flashing ... looks like the same rate at the calculate .. is there a way to stop that ..
cheers
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

What's happening is that the code is firing all the time the worksheet is updated. What you need to add a condition (or flag) to tell it to stop.

Pick a cell that is not populated by Bet Angel, for example Range("K5")

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("G1") = "In-play" and Range("K5")<>1 Then
    
    Range("K5")=1

    Application.EnableEvents = False

    Range("E120:K152").ClearContents
        
    Application.EnableEvents = True

    End If

End Sub
It'll fire once and then K5 will =1 so it won't fire again. You'll have to clear K5 to run it again
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

Brill, thanks again ...
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

Just as a matter of interest, how do you describe what happens in cell G1 when the race goes in play ..? ie what is the difference in typing in "In-Play" and the words just appearing in cell G1?
would you call it a change by formula? or fed from API ? or something else ?
working great btw
Thanks
Paul
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

G1 is automatically populated by Bet Angel, no need to put anything in there.

Excel is an event driven programe. When "In=play" appears in the cell, thats a worksheet change event, do a search on vba events, there are literally hundreds of events, each one can capture whats's going on in excel, from opening a workbook (workbook open event), selecting a different cell (selection change event), to pivot tables, before save,before close,etc.etc
sherman
Posts: 33
Joined: Mon Mar 24, 2014 10:58 am

ok, thank you for your time ..
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”