Reload quick pick list at midnight - help!

[loq]

Just a quick question regarding the example spreadsheet here.

If I load BA together with this spreadsheet AFTER all the races have been run (i.e. so there are no updates in Sheet1), how can I get it to reload the quick pick list at midnight and select the first market in the list?

Please note I am looking for a way to do this automatically. I'm sure there must be a simple solution to this but I am still learning VBA.

[gizzard]

If I've understood your problem correctly then the spreadsheet already does this for you.

It is triggered, initially, on the opening of the spreadsheet. Open the spreadsheet and the VBA (alt-F11) and the double-click "This Workbook" and make sure that the spreadshee that you are using has this code as this triggers the re-loading of the QuickPickList at midnight.

[loq]

gizzard, I appreciate your reply but I think you may be mistaken.

When the workbook is opened, "loadQuickPickList" is set to run at midnight.

"loadQuickPickList" simply sets the boolean value "triggerQuickPickListReload" to TRUE before setting "loadQuickPickList" to run at midnight again (the next time it is midnight).

The Worksheet_Change event in Sheet1 checks when "triggerQuickPickListReload" is TRUE before inserting "-3" in cell Q2, which is what actually triggers the reload of the Quick Pick List.

The problem is that this will never happen if there is no change in the worksheet, which there won't be if BA and the spreadsheet are loaded after all of that day's races. For example, all of yesterday's UK racing finished at 6pm - I loaded the spreadsheet with BA at 8pm but the Quick Pick List did not reload at midnight due to this problem.

I'm looking for a solution to this problem, so that the Quick Pick List is automatically reloaded at midnight (and the first market selected, of course) without user intervention.

[gizzard]

Ah, I'm beginning to see where you are coming from. I could be wrong but I think that you have BA set up so that you are only seeing today's events.
It's possible to set it to show tomorrow's events too. That way, when you first open the spreadsheet after the end of the last race of the day the first event for the next day is already available for the spreadsheet and this is the one that you would need to select.

To change this option to Options/Quick Pick List/markets and remove the tick against the third option "Only load today's events".

I hope that I'm understanding the problem now.

[loq]

Thanks gizzard, that really helps!

I wonder if this would work properly on those rare days when there is no racing...would it just keep ticking over until there IS racing again?

[gizzard]

Good. I wasn't sure if I still understood the problem. Such a simple solution too.
There are a few days such as Christmas Eve when this can happen but personally I wouldn't bother spending the time trying to get around it although there would be ways.
Just go and reload the pick list manually if you think this may have occurred.

I'm glad to have helped.