VBA for "In Play" timer

[allgreen]

Hi all, could someone please help me with some VBA to record the time the race goes in play e.g. When E2 changes to "In Play" paste the value of C2 to W2.

I did find a small snippet of code which copied E2 when the race goes in play but this duplicates the timer in C2 and doesn't 'freeze' the race start time. I have played around with this trying for a special paste but couldn't get it to work.

[PeteB]

There isn't a formula in C2, so if your destination cell keeps updating, it must be because your macro is running more than once.

So:
create a variable
Dim bGoneInPlay as boolean
and set it to true when you do the copy, and only do the copy if it is not already true. Remember to set it back to false when you load a new market.

[GaryRussell]

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.

[allgreen]

Thanks very much for the help Pete and Gary. And here was me expecting it was going to be something simple. I figure I would have still been trying to figure it out this time next year.

Gary, I put your code underneath some existing code which records a snapshot whenever my bets are updated and it has produced a compile error - 'Ambiguous name Detected : Worksheet_Change' - so evidently I need to combine these procedures or somehow define them? Needless to say I am a bit out of my depth here.

This is the existing code :


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub '16 cols for the API version, 13 for the WEB version of the software.
Application.EnableEvents = False 'essential as any change will result in a possible endless loop and possible lock-up

Dim runners As Integer
Dim sourceRange As Range
Dim destRange As Range

Dim profitSource As Range
Dim profitDest As Range
Set profitSource = Range("X5:X28") 'presumably the range where your proit/loss appears
Set profitDest = Range("Y5:Y28") 'blank range that isn't used on your sheet CHANGE IF NEED BE


runners = WorksheetFunction.CountA(Range("A5:A40")) 'counts the current race runners, upto 36 runners


If WorksheetFunction.Sum(profitDest) <> WorksheetFunction.Sum(profitSource) Then 'identifies change and snapshots
Range("A45") = "Previous Snapshots"
Set sourceRange = Range(Cells(1, 1), Cells(1, 1).Offset(runners + 4, 120)) 'This is the current region used
Set destRange = Range((Cells(Rows.Count, "A").End(xlUp).Offset(6, 0)), Cells(Rows.Count, "A").End(xlUp).Offset(runners + 10, 120)) 'captures 100 columns
destRange.Value = sourceRange.Value
End If

profitDest.Value = profitSource.Value 'replaces old data with new updated data

Application.EnableEvents = True
End Sub

[GaryRussell]

You are already using the change event so, just paste the following code before Application.EnableEvents = True. Also there is no need to create a module as the code you already have takes care of that part.

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

[allgreen]

Many thanks for your time Gary

[xraymitch]

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.

Hi Gary,

At the moment I am using Version 1.1.0.50
Auto load quick pick on program start
Auto select market 1 minute before the off
and your timer code in sheet1.

All works very well except when race off times are delayed or race off times clash with each other etc, ending up with at least 2 to 3 races per day being skipped.

When you get a moment from programming (April release is awesome ) can you help or point me in the right direction so that I can do the following:

I would prefer to
Load different venues in separate tab pages
and use your code in the separate sheets thus hopefully not skipping a race.

I briefly experimented with 3 sheets and inserted the appropriate line in each sheet.

Sheets("Sheet1").Select
Sheets("Sheet2").Select
Sheets("Sheet3").Select

Whilst this seemed to be checking the sheets it had the side effect of locking me out of Excel.

Cheers,

Ray

[sjaak1943]

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.

Can anyone tell me how to do this in my Excelsheet, step by step?
I don't know anything of macro's or modules!

Sjaak

[osknows]

In Excel 2003
1. Goto Tools/Options/Security/Macro Security and set macro security to medium
2. Close excel completely then re-open your workbook
3. Press Alt + F11 to get to the VBA editor
4. Double click the sheet name BA refreshes into on the left hand side of the VBA editor (eg Sheet1- it may be different if you have renamed it)
5. Copy the code Gary provided into the code window
6. Save your workbook, then link to BA

In Excel 2007
1. Goto Office Button/Excel Options/Trust Centre/Trust Centre Settings/MAcro Settings and set macro security 'Disable all macros with notification'
2. Close excel completely then re-open your workbook
3. Press Alt + F11 to get to the VBA editor
4. Double click the sheet name BA refreshes into on the left hand side of the VBA editor (eg Sheet1- it may be different if you have renamed it)
5. Copy the code Gary provided into the code window
6. Save your workbook, then link to BA

[danjuma]

In Excel 2003
1. Goto Tools/Options/Security/Macro Security and set macro security to medium
2. Close excel completely then re-open your workbook
3. Press Alt + F11 to get to the VBA editor
4. Double click the sheet name BA refreshes into on the left hand side of the VBA editor (eg Sheet1- it may be different if you have renamed it)
5. Copy the code Gary provided into the code window
6. Save your workbook, then link to BA

In Excel 2007
1. Goto Office Button/Excel Options/Trust Centre/Trust Centre Settings/MAcro Settings and set macro security 'Disable all macros with notification'
2. Close excel completely then re-open your workbook
3. Press Alt + F11 to get to the VBA editor
4. Double click the sheet name BA refreshes into on the left hand side of the VBA editor (eg Sheet1- it may be different if you have renamed it)
5. Copy the code Gary provided into the code window
6. Save your workbook, then link to BA

Following on from osknows well explained step-by-step above, I think (if I am not wrong) the following bit below would have to be inserted in a module and not in your sheet. So from the VBA editor, click "Insert" from the tool bar and then "Module" and copy and paste the statement in bold below into the module.

Create a new module and add the following line.

Public updating As Boolean

[sjaak1943]

Thanks Osknows.
Give it a try tomorrow!

Sjaak

[osknows]

Oh yes.. I didn't spot that bit

Danjuma is right, once you've taken my above steps then in the VBA editor

5a. Click Insert/Module
5b. Paste 'Public updating As Boolean' into the Module Code window
6. As above

[sjaak1943]

I tried today without reading the forum.
It did not work properly. now I have read the forum, i give it a try tomorrow.

Thanks guys you're very helpfull.

What if I want to add another code in the sheet, f.i. extract race length?

Sjaak

[osknows]

Just add any additional code into the subroutine eg

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

'insert code to update quick pick list
'insert code to extract race length
'insert more....

End Sub


[sjaak1943]

Thanks Osknows, nice to have "this helpers" on the forum.

Sjaak