Excel trigger help please.

[soccergeez]

Before I go about attempting to build this I wonder if someone can let me know if the triggers will function in this way and if I am going to need any VB (hopefully not) in order for this to work correctly.

I already have a spreadsheet wish I use to trade cricket manually and update throughout the match with current score, overs remaining etc etc to produce up to date odds for both teams. Obviously I want to automate the placement of bets using the trigger function in Excel. What I really need to know is can the triggers be set so that every time my spreadsheet produces new prices the bets previously submitted (if remaining unmatched) are instantly cancelled and new bets according to the new prices are submitted until these are superceded by the latest prices and so on ........

If anyone can advise me if this is possible I would be very grateful

Many thanks.

[Mitch]

Not sure if it can be done with spreadsheet formulas, but definately possible with vb.

Others on here have used web queries which I would have thought you use to update the score in the worksheet making it fully automatic, although I've never tried it.

[PeteB]

I agree - it's definitely possible - but I can't see how you'd do it without VB

With VB it won't be as hard as you might think - you basically need a block of code that is run each time your cricket sheet changes - store the last bet made, and if your sheet now suggests a different price, cancel the old bet and issue a new one. Might be a bit fiddly to check whether you previous bet has been taken, and decide what to do then, but depends what you want to do...

Agree with Mitch - you would probably want to automate your cricket sheet too - but you would need to find a web source of scores that is as quick as you listening to the radio and doing it manually! I would stick with your manual sheet, and automate the bet placement first, and then look at automating the cricket sheet too.

[soccergeez]

Thank you for the replies. Its good to know it can be done so I'll push on and get linking my current sheet into the trigger sheet as a start. If anyone is able to offer some code or advice on where to find some good info on building the code that would be fantastic. I think it really needs to be as straightforward as possible as I don't really have a clue with VB.

I would guess something that runs every time the values in the sheet alter as already suggested would be best. Presumably it would be possible to cancel all bets remaining (there will always be some as the sheet will submit 2 backs and 2 lays every time) then submit new bets then once the values change cancel all bets and submit new ones and keep this moving in a sort of "loop".

[Mitch]

As far as I know there isn't a trigger to cancel all bets, but you could get it to store the bet refs (if you can't leave them where they are) then cancel them one by one whenever you need to.

If you use the Worksheet_Change event handler to fire the code each time BA updates you could also use it to check to see if the changed cells are the ones you have updated yourself and act accordingly.

If you do a search for "intersect" on here you'll find some examples of that. Searching for "Worksheet_Change" should also throw up some code that could help.

I started with the basics by using the macro recorder in Excel to record common tasks then check to see how it had written the code for me.

You'll soon pick it up, and what you can't figure out just fire questions on here and you should get all the help you need.

[soccergeez]

Many thanks again Mitch, I shall be attempting to put something together over the bank holiday weekend, should have plenty of time as its apparently going to p1ss down with rain as usual. No doubt several rather basic qustions will soon be appearing on this thread!!