BA with 2 or more tabs showing the same market

[ddaann]

I'm using new beta version which is supposed to share API requests for the same market open in multiple tabs. I have two open tabs (plan to another some more) all linked to the same Excel file (each tab is different sheet). During prerace time I do lot of different things and it can get quite hectic, resulting in me forgetting to change tab2 to the same market as tab1. Autoselect market unfortunately doesn't suit my needs, as i sometimes change to different markets at different times. So my question is how can i make tab2 (and further tabs) show the same market as tab1. If that is currently not possible, are there any plans in future versions.
Many thanks
Dan

[GaryRussell]

The feature was only designed to shared requests when both tabs are displaying the same market. It does not force all tabs to show the same market. When you say you change to markets at different times, are you doing this manually or with a Q2 trigger?

Here is an example sheet which uses the "GO:" trigger in cell Q2 to change the market on sheet2 to the same as sheet1.
http://www.gruss-software.co.uk/Excel/SyncMarkets.xls

For more markets just edit the code in sheet1 module. For example to add a third sheet the code would read as follows.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        If [A1] <> currentMarket Then
            Application.EnableEvents = False
            ThisWorkbook.Worksheets("sheet2").Range("Q2") = "GO:" & [N3]
            ThisWorkbook.Worksheets("sheet3").Range("Q2") = "GO:" & [N3]
            Application.EnableEvents = True
        End If
        currentMarket = [A1]
    End If
End Sub


Unfortunately while testing this I discovered that the Beta version has a bug which causes the GO: trigger to be processed slowly. It sometimes takes up to 10 seconds to change markets. While this delay is occuring it's no longer being refreshed with the old market data though. This bug only exists in the Beta version. I will fix it in the next release.

[ddaann]

Thanks a lot Gary, this is what I was looking for.

[ddaann]

One slight problem Gary, as you can expect from Excel newbie like myself. This worked great on blank excel sheets, however on my sheet I already have one sub Worksheet_Change, and there is error message if i put them together. If i put your code in Sheet1 module nothing happens. Can you suggest something please. BTW I tried putting GO: with market id from sheet1 in q2 of sheet2, but for some reason formula gets deleted from q2 after one market change.
Many thanks
Dan

[GaryRussell]

One slight problem Gary, as you can expect from Excel newbie like myself. This worked great on blank excel sheets, however on my sheet I already have one sub Worksheet_Change, and there is error message if i put them together. If i put your code in Sheet1 module nothing happens. Can you suggest something please. BTW I tried putting GO: with market id from sheet1 in q2 of sheet2, but for some reason formula gets deleted from q2 after one market change.
Many thanks
Dan

You cannot use a formula to insert GO: in Q2 because it has to clear the cell to prevent it processing the trigger repeatedly. It has to be populated using VBA. There's no reason why you cannot get it to work with your current code, this is your only option. I cannot guess why it doesn't work with your code because I have no idea what the resultant code looks like. If it's not sensitive you can post it on the forum or you could send it to me in a private message and I will check it.

[ddaann]

The other code I am using is your code for inplay timer. When I try to mix those two I came to this:

Code: Select all

Option Explicit


Dim currentMarket As String
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
    If Target.Columns.Count = 16 Then
        If [A1] <> currentMarket Then
            Application.EnableEvents = False
            ThisWorkbook.Worksheets("sheet2").Range("Q2") = "GO:" & [N3]
            Application.EnableEvents = True
        End If
        currentMarket = [A1]
    End If
End Sub


This works ok except it keeps BA default refresh rate on sheet2 instead of sheet1 refresh rate. Any suggestions Garry. Thanks.

[GaryRussell]

The code would be more efficient if it read as follows.

Code: Select all

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Columns.Count = 16 Then
      Application.EnableEvents = False
      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
      If [A1] <> currentMarket Then
         ThisWorkbook.Worksheets("sheet2").Range("Q2") = "GO:" & [N3]
      End If
      currentMarket = [A1]
      Application.EnableEvents = True
   End If
End Sub

I'm not sure what you you mean regarding the refresh rate. This code does nothing with the refresh rate. Can you explain in more detail what your issue is?

[ddaann]

Sorted it with VBA code, thanks Gary