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
Register time of Supended
- 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.....
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?
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
Hi,ShaunWhite wrote: ↑Sat Jul 11, 2020 2:36 amIt'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.....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?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
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?
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
Application.EnableEvents = False ' => disable events
'The code ...
Application.EnableEvents = True ' => enable events
?
'The code ...
Application.EnableEvents = True ' => enable events
?
YES!... runnig well thanks a lot, just make some litle change but work!ShaunWhite wrote: ↑Sun Jul 12, 2020 9:44 pmApplication.EnableEvents = False ' => disable events
'The code ...
Application.EnableEvents = True ' => enable events
?
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
- 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.....