Q2 Full Stream

[Toandfro]

Hi

I want to control whether Full Stream is on or off, specifically I only want it on while a market is In Play. On the excel link page, if I enter a formula into cell Q2 such as =IF(E2="In Play","FULL-STREAM-ON",2.5), it will keep the refresh at 2.5 secs (with value 2.5 in the cell) until In Play, when it switches on Full Stream. But then the formula is immediately overwritten by BA with a blank cell (as soon as the trigger has fired I guess). Is there another way of doing this automatically within Preferences or somewhere?

Many thanks

[MollyMoo]

I think you would have to use VBA for this.

Enter the code below in the code section for the worksheet recording the odds:

Code: Select all

Private FullStream As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With ThisWorkbook.Sheets("Gruss") 'Replace Gruss with the name of your worksheet
If Not Intersect(Target, .Range("A1")) Is Nothing Then
FullStream = False
End If
End With
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
With ThisWorkbook.Sheets("Gruss") 'Replace Gruss with the name of your worksheet
IF .Range("Q2") = vbNullString Then
If .Range("E2") = "In Play" And Not FullStream Then
.Range("Q2") = "FULL-STREAM-ON"
FullStream = True
 ElseIf .Range("E2") <> "In Play" And FullStream Then
.Range("Q2") = 2.5
FullStream = False
End If
End If
End With
Application.EnableEvents = True
End If
End Sub

[MollyMoo]

Sorry - too many End Ifs:

Code: Select all

Private FullStream As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With ThisWorkbook.Sheets("Gruss") 'Replace Gruss with the name of your worksheet
If Not Intersect(Target, .Range("A1")) Is Nothing Then
FullStream = False
End If
End With
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
With ThisWorkbook.Sheets("Gruss") 'Replace Gruss with the name of your worksheet
If .Range("Q2") = vbNullString Then
If .Range("E2") = "In Play" And Not FullStream Then
.Range("Q2") = "FULL-STREAM-ON"
FullStream = True
 ElseIf .Range("E2") <> "In Play" And FullStream Then
.Range("Q2") = 2.5
FullStream = False
End If
End If
End With
Application.EnableEvents = True
End Sub


[Toandfro]

Thank you for this. Without being a vba expert - would this code run continuously? In which case it would be a concern as I have live triggers operating. Would an easier solution be for the BA program to accept a persistent value of say zero in Q2 which keeps Full Stream enabled. I'm not really seeing the logic of the FULL-STREAM-ON trigger as it stands, as without vba there seems no way to use it. Maybe Gary could help here, thanks

[MollyMoo]

It would run continuously in the sense that, every time your Excel bot connected to a new market, the full stream would be turned off and revert to 2.5 seconds. Then, when a market went in play, it would turn the full stream on.

[Toandfro]

brilliant, thank you so much

[alrodopial]

Would an easier solution be for the BA program to accept a persistent value of say zero in Q2 which keeps Full Stream enabled. I'm not really seeing the logic of the FULL-STREAM-ON trigger as it stands, as without vba there seems no way to use it. Maybe Gary could help here, thanks

A very good suggestion for Garry, maybe Q2:-11.1/Q2:-11.2 for on/off ?

Another way (with VBA) is two buttons that you will manually click to write "FULL-STREAM_ON / OFF" in Q2 (run once - not continuously)