Bet Angel - Spreadsheet / Excel chat : Clear a range of Cells when a Horse Race goes In-Play

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

Mon Jan 22, 2018 3:56 pm

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: 393
Joined: Wed Apr 15, 2009 11:00 pm

Mon Jan 22, 2018 6:08 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: 17
Joined: Mon Mar 24, 2014 10:58 am

Mon Jan 22, 2018 6:31 pm

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: 393
Joined: Wed Apr 15, 2009 11:00 pm

Mon Jan 22, 2018 6:58 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: 17
Joined: Mon Mar 24, 2014 10:58 am

Mon Jan 22, 2018 7:16 pm

ok thanks again, seems to do the job great ...
Cheers & thanks very much for your help
Paul

sherman
Posts: 17
Joined: Mon Mar 24, 2014 10:58 am

Mon Jan 22, 2018 7:24 pm

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: 393
Joined: Wed Apr 15, 2009 11:00 pm

Mon Jan 22, 2018 7:38 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: 17
Joined: Mon Mar 24, 2014 10:58 am

Mon Jan 22, 2018 7:43 pm

Brill, thanks again ...

sherman
Posts: 17
Joined: Mon Mar 24, 2014 10:58 am

Wed Jan 24, 2018 9:27 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: 393
Joined: Wed Apr 15, 2009 11:00 pm

Wed Jan 24, 2018 12:02 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

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 2 guests