The macro receives distortion after midnight

[bines]

Hi gary,
I am a user in the macro this that gave -
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

The game started according to local clock in 23.00 tonight,After the midnight.The datum of the seconds passed to the minus with big number if can improve the macro?

[osknows]

if you want your timer to rest at midnight use

Code: Select all

'reset timer
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Cells(2, 5) = "In Play" Then
If Cells(1, 27) = "" Or Cells(1, 27) > Cells(2, 3) Then Cells(1, 27) = Cells(2, 3)
If Cells(1, 27) <> "" Then Cells(1, 2) = 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, 2) = ""
End If

Application.EnableEvents = True
End Sub



If you want it to continue incrementing after midnight use

Code: Select all

Public Count As Integer
'continue increment timer
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Cells(2, 5) = "In Play" Then
If Cells(1, 27) = "" Then
    Cells(1, 27) = Cells(2, 3)
    Count = 0
End If

If Cells(1, 27) > Cells(2, 3) Then
 Cells(1, 27) = Cells(2, 3)
 Count = Count + Cells(1, 2) + 1
End If

If Cells(1, 27) <> "" Then Cells(1, 2) = DateDiff("s", Cells(1, 27), Cells(2, 3)) + Count
End If
If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
Cells(1, 27) = ""
Cells(1, 2) = ""
End If

Application.EnableEvents = True
End Sub


[bines]

osknows, thank you !