Code help and requests

[vanderbet]

Hi to all. Dear developers, please forgive me if I re-invent the wheel due to my igmorance ('cause I am a Freshman in the Science of Betting), but loving the program I cannot resist some suggestions requiring maybe some slight modifications, making our life easier.
First thing that bothers me I will call it "In Play actual start time". I bet you 80% of trigger bettors thought once in their lifetime to make a trigger based on this somehow, either by counting down the actual duration time of the race or I don't know maybe by counting up. So, I think 3 cells indicating pretty much the same aspect of time (B2,C2,D2) and only one flag (E2) don't help much, forcing us to spend time and effort to debugging the dreadful (for me at least, who I don't claim to be VBA genius exactly) trigger events code. Would you please consider to dedicate a little corner e.g C2 or G2 or any adjacent cell for a frozen instance-capture of the "In Play" moment, helping us to re-adjust our code rely on that ?
Second thing is the difficulty I have to locate something in Preferences-Options to keep and remember the cells and the sheets for Excel logging, for those who prefer to log in other but A1 cell and other than the same worksheet which comes as a default the Bet Results. I guess must be somewhere but I can't find it. I tried in the "Multiple sheets quick link" but I failed. Last thing that comes to my mind is the effort I made to print some parts of program's manual. It was quite an adventure to locate it where was hidden and some pages are in Flash format and they are non printable.
If I'm not making terrible mistakes and these things are not really issues would you please consider them in your next version, making it probably a wonderful piece of software ?
Speaking of the devil, - the trigger events - could you please someone, kind enough, enlighten me of what's wrong with this code below ? Thanks in advance. And sorry for my English.

Code: Select all

Option Explicit
'------------------------(  The logged race begins from A15 cell  )-----------------------------------------------------------------------------------
Dim currentMarket As String, IPstat As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim marketChanged As Boolean

    If Target.Columns.Count = 16 Then
        If [A15] <> currentMarket Then
            currentMarket = [A15]
            marketChanged = True
            Application.EnableEvents = False
            SHEET_PREP  'calling another procedure
           
            '---------------- Capturing the moment of "In Play" status an recording it in J7 in sec----------------------------------
            If [J7] = "" And [E16] = "In Play" And [F16] = "" Then
                [J7] = (Hour(Mid([D16], 2, 10)) * 3600) + (Minute(Mid([D16], 2, 10)) * 60) + Second(Mid([D16], 2, 10))
            Else
                [J7] = ""
            End If
            '--------------------------------------------------------------------------------------------------------------------------------------------------------
            Application.EnableEvents = True
        Else
            marketChanged = False
        End If
    End If
End Sub


[mak]

Hi
i don't know much about vba, so I can't comment or guess what is wrong with your code...

regarding in play start time though you can use Gary's code below

-------------------------------------------------------------------------------

Insert the following code into sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
If updating Then Exit Sub
updating = True
If cells(2, 5) = "In Play" Then
If cells(1, 27) = "" Then cells(1, 27) = cells(2, 3)
If cells(1, 27) <> "" Then cells(1, 28 ) = DateDiff("s", cells(1, 27), cells(2, 3))
End If
If cells(2, 5) <> "In Play" And cells(2, 6) <> "Suspended" Then
cells(1, 27) = ""
cells(1, 28 ) = ""
End If
updating = False
End Sub

Create a new module and add the following line.

Public updating As Boolean

Cell AA1 will contain the time the market went in play and cell AB1 will contain the number of seconds from when the market went in play

[vanderbet]

Hi Mak. I will give a try and I will tell you if it works. The module must be in the same sheet of course in order to trigger. Right ? I really appreciate your help.

[vanderbet]

Another thing. Trying the triggers with late night American races and this morning with RSA, I found out that the cell E2 doesn't change, neither the screen flashes "Suspended" when goes "In Play". Am I missing something here, or did I deactivate such a useful info by mistake. Thanks again.

[mak]

Regarding the vba code, yes, you'll have to adapt it into your code
I would suggest to make for start, a new xlsheet only with this code just to see how it is working, and after you can adapt it to your code. Don't forget to create the new module also...

"""Create a new module and add the following line.

Public updating As Boolean """

Sorry, but I don't follow the particular markets you are referring so I can't comment...

[vanderbet]

Yes. The code alone it worked fine. And you are the most helpful guy Mak. The problem begins though when I am trying to add some more tasks in this particular code, e.g. when I want to trigger some actions when the obvious ranked first favourite becomes second and vice versa, indicating a risky bet. In that case my little experience tells me that trigger event codes are very ill-tempered ...horses, not tolerating the extra load, that's why I would prefer to have my trigger codes light as possible. And that's why I would keep suggesting to the developers a dedicated cell for the Inplay time count. Thanks a lot again.