Need To Stop my Excel Sheet Triggering Bets

[forum1]

I am looking for a way to stop my excel sheet firing automatically bets when the market gets suspended. When the market goes in-play again I will be required to intervene manually in order to allow it firing bets again.

For example, if market gets suspended cell A10 gets value No. I must change content of cell A10 manually to Yes if i want it to start triggering again.

Is VBA required for this?

[Graham]

cell F2 will show "Suspended" when the market is suspended, or "Closed", when the market is closed. Just check it's blank as part of the formula for your trigger.

[forum1]

cell F2 will change from "Suspended" to blank when the market reopens. I don't want it to fire a bet before I intervene manually (=make sure that flow of information is Ok after what caused suspension).

[Graham]

Sorry misunderstood, VBA is required


Private Sub Worksheet_Calculate()
If Cells(2, 6) = "Suspended" Then Cells(10, 1) = "No"
End Sub

[forum1]

I am sorry for the late response.

I've incorporated these 2 lines as well as changed my trigger conditions (to trigger bets according to the content of cell A10) and I've noticed that it works in about 90% of the cases (huge improvement but not 100%) that there is a "Suspended" market.

I consider strange that although I have 3 sheets connected to the same market (3 different Tabs open on it) sometimes 2 of them have their A10 cells filled with "No" but the third does not. Is it a symptom of busy/"tired" excel/computer?

[Graham]

It's possible that you "miss" the suspension due to it occurring between refreshes. What refresh rate are you using, and is it the same for all tabs?

[forum1]

Refresh rate 0.6 sec for all three tabs while the market during these odd cases stays suspended for about 15sec.

I've also noticed that sometimes Sheets 1 and 2 change cell content to "No" while Sheet 3 doesn't while another time it can be Sheets 1 and 3 while 2 doesn't.

[Graham]

Strange, don't know why that should be. Are all the sheets in the same workbook? Where did you put the code?

[Captain Sensible]

If you're intervening manually when in play why not have your code set the cell to NO when E2 shows "In Play"?

You can set other flags within your code to stop it changing back after you've started betting inplay and reset evrything back either when you change markets or using another trigger cell.

[Captain Sensible]

Only need to amend the code to something like

Private Sub Worksheet_Calculate()
If Cells(2, 5) = "In Play" AND Cells(10, 1) = "" Then Cells(10, 1) = "No"
End Sub

You'd just set the trigger not to bet if Cells(10, 1) is "No" and to resume betting just stick anything in the cell "y" then clear the cell again ready for the next market

[forum1]

Answering to Graham:
I've copied these two lines as well as changed my trigger conditions in the code (object sheet 1,2,3) of these three sheets I'm referring above. It is still below 100% and the sheet that cell's content does not change is not always the same.
My only explanation can be "tired'' computer.

To captain Sensible:
Not sure I understood his advice; however I don't think what he's proposing faces the reason why Graham's routine does not work in my sheet everytime.

[Captain Sensible]

No problem , the suspend indicator is only there for a fraction of a second so even at 0.6 seconds refresh it's easily missed especially if excel is busy updating and slowing further refreshes.

[Captain Sensible]

I'm only talking from a Horseracing point of view so obviously if you're playing football the suspend will stay for a lot longer

[Graham]

Seems strange that one sheet doesn't update while the rest do, especially since the market is suspended for 15 seconds. I do not have the first idea why. Sorry.

Can I ask if on any ocassion that all 3 sheets miss the suspension?

I have no idea of your setup forum1, but i am assuming that :

you have one instance of BA open with the same market on 3 tabs, linked to 3 sheets in one excel workbook, all with the same refresh rate.

And that once you have made some manual change after a suspension you wish to restart your automated betting, this will be conditional on the contents of cell A10.

Am I correct, because it would be easy to come up with a simple workaround (although I cant see why this is not working for you 100 percent)

[forum1]

Can I ask if on any ocassion that all 3 sheets miss the suspension?

no, only one of the three (not the same)

you have one instance of BA open with the same market on 3 tabs, linked to 3 sheets in one excel workbook, all with the same refresh rate.

this is right

And that once you have made some manual change after a suspension you wish to restart your automated betting, this will be conditional on the contents of cell A10

actually, I don't want to make any change, but I want to make sure that my algorithm is still valid (related to what caused the suspension). In case it isn't, there is no reason to fire bets automatically so your VBA code that has changed the A10 cell content to "No" perfectly blocks my sheet from firing bets (unfortunately, not always