Register time of Supended

Example spreadsheets and comments on example spreadsheets.
Post Reply
SPina
Posts: 7
Joined: Sun Mar 11, 2012 10:58 pm

Hi,

I´m trying to make a VBA code to register the moment that suspended live soccer game disappear and returns to In-play.

Im try this code but dont work, only work when i change manualy... can some one help me?...

Private Sub Worksheet_change(ByVal Target As Range)

On Error GoTo out
If Target.Column = 8 And Target.Row = 1 Then
Range("i3").Value = VBA.Time
End If

out:
End Sub



Also see th epicture in attachment
You do not have the required permissions to view the files attached to this post.
SPina
Posts: 7
Joined: Sun Mar 11, 2012 10:58 pm

No one to help me?
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

It's a while since I did any VBA but the way the sheets update I think Target might be returning a range rather than descrete values for col and row, ie 8 and 1. You could check that with debug.

But I think I'd start by keeping it simple and say something like.....

Code: Select all

CurrStatus = Trim(Range("H1").Value)
If CurrStatus <> PrevStatus Then
    Range("StatusTimeChanged") = Vba.time 
    If CurrStatus = "Suspsended" and PrevStatus = "" Then 
         rem .....  anything else you want to do
    End If
    If CurrStatus = "In-Play" and PrevStatus = "Suspended" Then 
         rem .....anything else you want to do
    End If
    etc etc
End If
PrevStatus = CurrStatus 
It's not pretty or clever but it eliminates an unknown (ie what "Target" might contain) and you can at least break into the code with a breakpoint (F9) and step your way through it (F8 ?) it and see what's happening. Once you're happy with what's going on then maybe you could wrap it all in your "If Target.Column = 8 And Target.Row = 1 Then.... End If" to speed it up a bit?
SPina
Posts: 7
Joined: Sun Mar 11, 2012 10:58 pm

ShaunWhite wrote:
Sat Jul 11, 2020 2:36 am
It's a while since I did any VBA but the way the sheets update I think Target might be returning a range rather than descrete values for col and row, ie 8 and 1. You could check that with debug.

But I think I'd start by keeping it simple and say something like.....

Code: Select all

CurrStatus = Trim(Range("H1").Value)
If CurrStatus <> PrevStatus Then
    Range("StatusTimeChanged") = Vba.time 
    If CurrStatus = "Suspsended" and PrevStatus = "" Then 
         rem .....  anything else you want to do
    End If
    If CurrStatus = "In-Play" and PrevStatus = "Suspended" Then 
         rem .....anything else you want to do
    End If
    etc etc
End If
PrevStatus = CurrStatus 
It's not pretty or clever but it eliminates an unknown (ie what "Target" might contain) and you can at least break into the code with a breakpoint (F9) and step your way through it (F8 ?) it and see what's happening. Once you're happy with what's going on then maybe you could wrap it all in your "If Target.Column = 8 And Target.Row = 1 Then.... End If" to speed it up a bit?
Hi,

I try with that code but he gives a error Out of stack space (Error 28), i think is because he is in lopping. Do you have a solution?
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Application.EnableEvents = False ' => disable events

'The code ...

Application.EnableEvents = True ' => enable events

?
SPina
Posts: 7
Joined: Sun Mar 11, 2012 10:58 pm

ShaunWhite wrote:
Sun Jul 12, 2020 9:44 pm
Application.EnableEvents = False ' => disable events

'The code ...

Application.EnableEvents = True ' => enable events

?
YES!... runnig well thanks a lot, just make some litle change but work!

Private Sub Worksheet_change(ByVal Target As Range)

Application.EnableEvents = False


CurrStatus = Trim(Range("H1").Value)

If CurrStatus = "Suspended" Then

Range("I3").Value = VBA.Time

End If


Application.EnableEvents = True

End Sub
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

That will keep refreshing the time in cell i3 while the status remains suspended. I thought you wanted the time it becomes suspended. That's why I checked for a change of status, ie curr <> prev. If you're happy with how yours works then you don't need the variable CurrStatus. Just say If Trim(Range("H1").Value) = "Suspended" Then.....
Post Reply

Return to “Bet Angel - Example spreadsheets”