Time Triggered Market Selection

[AndrewP]

I am doing some research which requires selection of markets at a specific time of day (not at a specific time before each race). I want to use BA for this so that the information goes into a spreadsheet that will be set up to place bets.

I can link the spreadsheet to BA and by using some VBA code to put -1 in cell Q2 I can force BA to cycle through the various races in a single pick list.

My problem is in starting this process. The help file suggests that if -1 is put in cell Q2 and no markets are selected it will select the first market in the pick list. This doesn't seem to be the case, at least for me.

I can set BA to select the market at a specific time before the off that equates to the time I want (e.g. if I want it to happen at 11.00 a.m. and the first race is at 2.00 p.m. I can set it to select 3 hours before). If I do this and BA selects the first race and my code cycles through all the other races, will BA cycle through them all again three hours before each race?

Any help with this will be greatly appreciated.

Andrew

[osknows]

Hello,

This thread may help you with identifying when the first/last item in the quickpick list is selected http://gruss-software.co.uk/forum/viewtopic.php?t=3744

You just need to update your code to reset back to the first item

If you need code to update at specified times independently from BA you can use the following

Put this in the THisWorkbook object

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)

     Application.OnTime dTime, "Timercode", , False

End Sub

Private Sub Workbook_Open()

  Application.OnTime TimeValue("18:15:00"), "Timercode"

End Sub


This code runs a procedure named 'Timercode' at 18:15 only

Alternatively, you could use

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)

     Application.OnTime dTime, "Timercode", , False

End Sub

Private Sub Workbook_Open()

  Application.OnTime  Now + TimeValue("00:00:30"), "Timercode"

End Sub


which will run Timercode after 30secs after opening workbook

And place this in a new module object

Code: Select all

Public dTime As Date

Sub Timercode()

dTime = Now + TimeValue("00:00:30")
Application.OnTime dTime, "Timercode"

MsgBox ("Insert Your Code Here!")

End Sub


which is called from the first timer function and then re-triggers for another 30 seconds

If you remove the first two lines the procedure will only be called once only from the workbook open event

Os

[AndrewP]

Thanks for the link and the snippets of code Os.

Unlike the person posting in the link my problem is not with reaching the end of the pick list of events but in getting started in the first place. I can't seem to force BA to select the first race in the list automatically.

I'm not sure I can do so using your code either.

I've discovered that if I use 'auto select markets at specific time before the off' then the first event loads but I cannot then cycle through the list by using -1 in cell Q2.

Andrew

[negapo]

-5 in Q2 selects the first market so you can, for example, browse the markets of the quick pick list (with -1 and -5). Hope it helps.

[osknows]

Ah I see.. I think you have to select a market manually first from BA to first allow excel and BA to link then you can do everything you need by code. -1 in Q2 definitely does work

I had a problem where the picklist was forwarding twice as my code placed a -1 in Q2 then BA cleared it out on a refresh and my code then placed it again. I now use the following code to forward races after the previous race has ended.

Code: Select all

 If Target.Columns.Count = 16 Then

    'forward quick pick race on after race ended
    If Cells(2, 5).Value = "In Play" And Cells(2, 6).Value = "Suspended" And UCase(Cells(1, 17).Value) = "Y" And nextracetrigger = 0 Then
    Application.EnableEvents = False
        nextracetrigger = 1
        lastrace = Cells(1, 1).Value
        Cells(2, 17).Value = -1
     Application.EnableEvents = True
    End If
   
    If lastrace <> Cells(1, 1).Value Then
    Application.EnableEvents = False
        nextracetrigger = 0
        Cells(2, 17).FormulaR1C1 = "=IF(OR(RC[-11]=""Suspended"",RC[-11]=""Closed""),1,IF(ISERROR(IF(AND(HOUR(RC[-13])=0,MINUTE(RC[-13])<=1),0.2,1)),0.2,IF(AND(HOUR(RC[-13])=0,MINUTE(RC[-13])<=1),0.2,1)))"
    Application.EnableEvents = True
    End If
end if


This code forwards the quick picklist by 1 after a race has ended (In Play & Suspended and a switch in Q1 = "y" and nextracetrigger = 0). Change the conditions for your own requirements

It then tests if the racename has changed and sets the nextracetrigger back to 0 to avoid retriggering when BA clears Q2 on refresh

The big formula at the end just places a formula back into Q2 which sets my refresh rate as 1sec or 0.2 secs a minute before the race starts

[AndrewP]

Thanks for your help guys. The answer was staring me in the face all the time. RTFM as they say.