QuickpickList loading from vba - not "reload"

[Fixador]

'Reload' scenerio here:-

http://www.gruss-software.co.uk/Excel/A ... ckList.xls

As I understand it, the above is applicable to reload the list on a day to day roll over basis - SO, there is already data in cell A1 etc - so procedures under a WorkSheet_Change event will work.

But what if : There is no prior data in cell A1 etc - procedures under worksheet_change wont fire off - as there is yet no data in cells A1 etc.

This follows from query in

http://gruss-software.co.uk/forum/viewtopic.php?t=4657

Whats the best strategy to achieve repeated Q2 triggers to ensure population of the quickpicklist and load the first race.

Why do i want to this ? pc set up daily, BA started say 8 am, yet until 1 pm - nothing is wanted to happen.

Thought there might be a more 'elegant' solution than populating quickpicklist & loading first race via BA controls at 8 am...... or have i got my head up my bottom ?

[GaryRussell]

You are right. My example is flawed in that respect. I could add an extra option so that -3.1 etc. also select the first market it in the list automatically. This would mean you wouldn't have to use the -5 trigger, just -3.1. I will release an update tomorrow morning.

[Fixador]

Hi Gary,

Fiddling with OnTime function started from a cmd, ( which calls at say 1 pm )

the called sub ( from OnTime ), the sub tests if A1 is empty.

If yes: then fires Q2 trigger -3.1, then from a 2nd OnTime - 30 seconds later Q2 trigger -5, Then another OnTime calling the orginal sub again.

The OnTime function in the cmd - calls the following sub

Code: Select all

Sub MySubTest()
Dim sht as Worksheet
Set sht = ThisWorkbook.Worksheets(1)
If isEmpty(sht.Cells(1,1))= True then
    sht.cells(2,17)= -3.1
    Application.OnTime Now + TimeValue("00:00:30"), "MyFirstRace"
    'call again so can test A1 again
    Application.OnTime Now + TimeValue("00:01:00"), "MySubTest"
end if
End Sub



running out of races to test it on today !

[joe lively]

You are right. My example is flawed in that respect. I could add an extra option so that -3.1 etc. also select the first market it in the list automatically. This would mean you wouldn't have to use the -5 trigger, just -3.1. I will release an update tomorrow morning.

Hi Gary, i've been struggling with something similar myself so a trigger to refresh and select the first market in the list would be a real help.

Cheers.

[joe lively]

Actually Gary it is -4 I have been using, obviously at the moment it puts each venue into a seperate tab but would it be difficult to make it also select the first market in the list?

Thanks agin.

[GaryRussell]

Actually Gary it is -4 I have been using, obviously at the moment it puts each venue into a seperate tab but would it be difficult to make it also select the first market in the list?

Thanks agin.

I will also make it select the first market on all tabs.

[GaryRussell]

I have added the new option to version 1.1.0.66f.

Tick “Quick pick reload triggers select first market” and triggers like -3.1 etc. will select the first market after loading the quick pick list.

[joe lively]

Thanks Gary, I have version 1.1.0.66f but I can't see the option, i'm looking in options > preferences > Quick pick list /markets

Will it auto update with the new option or do I have to reinstall?

Thanks

[GaryRussell]

The option is in the popup displayed when you select "Log current prices" from the Excel menu.

[joe lively]

Sorry Gary, ignore that I just realised it was in the excel logging options.

[joe lively]

Gary, i'm probably asking a bit too much here but I'm only interested in loading the following days markets into new tabs.

Obviously at the moment -4 in Q2 seperates all venues, this means I have to have twice as many worksheets and tabs open than I need. I understand that this is a very specific request and probably not worthy of adding into the software but I wondered if you could advise on a possible solution to this?

Thanks.

[GaryRussell]

Gary, i'm probably asking a bit too much here but I'm only interested in loading the following days markets into new tabs.

Obviously at the moment -4 in Q2 seperates all venues, this means I have to have twice as many worksheets and tabs open than I need. I understand that this is a very specific request and probably not worthy of adding into the software but I wondered if you could advise on a possible solution to this?

Thanks.

Is there a reason why you can't fire the -4 trigger late in the evening? Only markets from tomorrow will exist then.

[joe lively]

Yes the reason being that i'm trying to get on in these markets as soon as they open (or as close as possible). Basically i'm trying to get my money to the front of the queue at a certain price.

Similar to the guys that try and get to the front of the queue at 1.01 as soon as the market opens except it is other prices that interest me.

I suppose i'm looking at a way to 'refresh the quick pick list with tomorrows markets'.

Your thoughts would be appreciated.

Thanks.

[GaryRussell]

The next update will allow you to use "-3.1t", "-4t" etc. which will only load events from the following day onwards.

[Yorkie]

Can "Quick pick reload triggers select first market" be added to the excel multiple sheets loader please.

Cheers

Yorkie