REQUEST:
Whenever the Market linked to an Excel worksheet changes there should be an option to reset the worksheet clearing all residue data from the previous Market.
REASONING:
Yes, I know it's (supposed to be) possible to clear the cells using VBA but I can never get it to work and after 3 postings in the Excel forum I'm no closer to the solution!
viewtopic.php?f=19&t=21026
viewtopic.php?f=19&t=20947
viewtopic.php?f=19&t=19957)
All I want to do is clear the spreadsheet of the previous race/runner data when the market changes. Forum members have been very helpful in trying to help but as yet without success.
I can understand that you want to protect people from shooting 100's of unintentional bets into the market by accident but I cannot understand why you should think that someone would want to carry over the Bet Status (and other runner specific cells) from an old redundant market to a new active market. And if anyone wants to do so then why not have a checkbox that gives the option … "Do you want to clear the workheet when the market changes … Yes/No". This would only apply at change of Market so the risk is only for 1 bet for each selection because the "PLACED" protection would still apply.
Many (me included) come to BA as competent Excel users but have never needed to resort to VBA until exploring BA. As an immediate fix why not post a subroutine to achieve this that people can download and install into their worksheets if required.
Clearing the Bet Status Cells in Excel
- firlandsfarm
- Posts: 2688
- Joined: Sat May 03, 2014 8:20 am
Thanks for your support Atho, the more it's referred to as "automatic" the more this is a software bug. Without such a facility the extent of it's ability to automate is for 1 bet per selection row per session! and if you are a Favourite Better (always wagering on the top row) it means just one bet per session!
Having to be proficient in VBA should not be a "must have" to be able to use the Excel interface IMHO. This issue has been rumbling on for years and whilst the Forum provide numerous solutions, again IMHO, the software provider BA should be providing the solution.
I find BA`s silence on this more annoying than anything as usually they add a 12d worth on most subjects which is both informative and refreshing.
I find BA`s silence on this more annoying than anything as usually they add a 12d worth on most subjects which is both informative and refreshing.
The whole point of Excel is so that you can customize it the way you want. There are several different snippets of code out there that can achieve a clear status and several users have pointed you in the right direction. A few people have taken the time to help you get your specific demands accomplished. I think it's a bit unreasonable for you to expect there to be some implementation out there which adresses specifically the way you want it. I had zero experience in coding when I managed to sort it out for myself in the way that I wanted. You don't have to be proficient, all it takes is a little patience and research and thought. It's really not something to kick up a fuss over. If you can't achieve it for yourself find a developer to do it for you for 10 quid.
- firlandsfarm
- Posts: 2688
- Joined: Sat May 03, 2014 8:20 am
I understand the point of VBA and customisation but I'm afraid we are just going to have to disagree over what is a customisation and what is a fundamental omission! To me it's like having a driverless car that will take you to your destination but to get back home you need to know how to re-code the navigation software! I fully appreciate the help I have been offered and have thanked people for their assistance and just because I think such a fundamental facility should be incorporated in the software does not mean I am not working on trying to get it to work but so far nothing suggested has done the trick. And I don't see what is "specific" about my "demands" (I would prefer requests) … Anyone and everyone who uses BA/Excel has to clear down the runner cells whenever the market changes, I see that as fundamental, not specific. I have given it lots of patience, research and thought … I have done so 3 times and I'm not kicking up a fuss … this is a section on the forum asking for suggestions, that is what I have submitted, a suggestion. I have made the suggestion and given my reasons, I'm sorry if you think that is making a fuss, another thing we will just have to disagree on.sa7med wrote: ↑Tue May 19, 2020 12:57 pmThe whole point of Excel is so that you can customize it the way you want. There are several different snippets of code out there that can achieve a clear status and several users have pointed you in the right direction. A few people have taken the time to help you get your specific demands accomplished. I think it's a bit unreasonable for you to expect there to be some implementation out there which adresses specifically the way you want it. I had zero experience in coding when I managed to sort it out for myself in the way that I wanted. You don't have to be proficient, all it takes is a little patience and research and thought. It's really not something to kick up a fuss over. If you can't achieve it for yourself find a developer to do it for you for 10 quid.
VBA Excel
Featured snippet from the web
VBA (Visual Basic for Applications) is the programming language of Excel and other Office programs.
As described above, I have no desire to learn programming to provide a solution for something that has needed sorting for 10 years.
It seems you have a solution but could not bring yourself to post a fully functioning worksheet to kill this issue once and for all. It would be very generous of you and kindly received by many over the next few years but does not alter the fact that the software provider, again IMHO, should be providing the solution that is compatible and has been tested within the platform it is to be used in conjunction with. My 12d worth.
Firlands can obviously type faster...
Featured snippet from the web
VBA (Visual Basic for Applications) is the programming language of Excel and other Office programs.
As described above, I have no desire to learn programming to provide a solution for something that has needed sorting for 10 years.
It seems you have a solution but could not bring yourself to post a fully functioning worksheet to kill this issue once and for all. It would be very generous of you and kindly received by many over the next few years but does not alter the fact that the software provider, again IMHO, should be providing the solution that is compatible and has been tested within the platform it is to be used in conjunction with. My 12d worth.
Firlands can obviously type faster...
It's just that there's 1000s of ways to implement it under many different conditions and you're asking for it to be done the way you want it. If you upload a version of your excel sheet and describe in steps exactly what you want to be done with it I can have a go.
Spot on. We occasionally have requests for cell clearing into support and, when clarified, every request is different.
The idea of the green coloured cells in Excel is that they can be populated with customer's own formulae - if those cells were cleared when switching markets then all the formulae would be wiped out too. So you'd create a situation where the spreadsheet required manual intervention on each market, or required VBA code to populate it (in which case why not also use VBA to clear it exactly as required).
Some ask for just the status column to be wiped when switching markets, but that would cause any betting commands left in columns L/M/N to trigger immediately when given a market. That may suit a few people, but certainly not everyone, and you'd think that Automation Rules could do the same without even bothering with Excel for simple scenarios like that. Others ask for Status and Odds to be cleared, but not Stake. And I'm sure there must be some poeple that want Stake cleared but not Odds!
We're happy to take a look at auto-clearing if we can have a consensus as to how it should work, but we're yet to see more than a few people that want the same behaviour. Normally offering a solution like this, just moves the problem somewhere else until VBA is the only solution. Excel is wonderful with VBA, but limited without it.
Perhaps there could be an automation rule to clear them in guardian so that people can set up their own rules for clearing. Just a thoughtBet Angel wrote: ↑Tue May 19, 2020 2:35 pmSpot on. We occasionally have requests for cell clearing into support and, when clarified, every request is different.
The idea of the green coloured cells in Excel is that they can be populated with customer's own formulae - if those cells were cleared when switching markets then all the formulae would be wiped out too. So you'd create a situation where the spreadsheet required manual intervention on each market, or required VBA code to populate it (in which case why not also use VBA to clear it exactly as required).
Some ask for just the status column to be wiped when switching markets, but that would cause any betting commands left in columns L/M/N to trigger immediately when given a market. That may suit a few people, but certainly not everyone, and you'd think that Automation Rules could do the same without even bothering with Excel for simple scenarios like that. Others ask for Status and Odds to be cleared, but not Stake. And I'm sure there must be some poeple that want Stake cleared but not Odds!
We're happy to take a look at auto-clearing if we can have a consensus as to how it should work, but we're yet to see more than a few people that want the same behaviour. Normally offering a solution like this, just moves the problem somewhere else until VBA is the only solution. Excel is wonderful with VBA, but limited without it.
Sa7med, that`s an excellent idea.
BA, if you already have solutions of many kinds assuming you have resolved the requests, why not bundle them ALL into a folder with a description of what each one does so the user can select the one that looks to be the best fit.
BA, if you already have solutions of many kinds assuming you have resolved the requests, why not bundle them ALL into a folder with a description of what each one does so the user can select the one that looks to be the best fit.
- firlandsfarm
- Posts: 2688
- Joined: Sat May 03, 2014 8:20 am
Thanks sa7med, it seems to me that I am trying to achieve something so basic (and fundamental) that people aren't really grasping what it is! I'm not wanting code to clear the cells, I have a sub for that ... I'm just looking for how I call that sub to clear the cells. I have a standard BA/Excel multi sheet workbook reduced to just two sheets (for two markets) with some cell formula in one of the sheets making the comparisons and triggering the bets (the 'active' sheet). The only VBA is this sub to clear the standard cells …sa7med wrote: ↑Tue May 19, 2020 1:44 pmIt's just that there's 1000s of ways to implement it under many different conditions and you're asking for it to be done the way you want it. If you upload a version of your excel sheet and describe in steps exactly what you want to be done with it I can have a go.
Code: Select all
Option Explicit
Sub ClearCells()
'
' Clear all cells on Market Change
'
' Keyboard Shortcut: Ctrl+Shift+C
'
' ClearRunners
Range("B9:K68").Select
Selection.ClearContents
'
' ClearBetsData
Range("O9:AE68").Select
Selection.ClearContents
'
' Clear bet placement confirmations
Range("O6,L10,L12,L14,L16,L18,L20,L22,L24,L26,L28,L30,L32,L34,L36,L38,L40,L42,L44,L46,L48,L50,L52,L54,L56,L58,L60,L62,L64,L66,L68").Select
Selection.ClearContents
End Sub
Code: Select all
Private Sub Worksheet_Change(byval target as range)
Sheets("Bet Angel P").Activate
If Not Intersect(target.address, Range("$B$1")) is Nothing Then ClearCells()
End If
End Sub
I have tried (another popular Internet solution) ...
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Bet Angel P").Activate
If Target.Address = "$B$1" Then
Call ClearCells
End If
End Sub
And I have tried this code (also popular on the Internet) …
Code: Select all
Public Sub Worksheet_Change(ByVal target As Range)
Dim intersection As Range
Set intersection = Intersect(target, Range("$B$1"))
If Not intersection Is Nothing Then Call ClearCells()
End Sub
I have tried using all Market variable cells to ID a new market without success. My latest attempt this afternoon after Aus has finished is to incorporate a system clock using VBA and comparing that with the cell F3 (Event Start) and clearing the cells when the scheduled start time has passed. I have read that this can be resource hungry but I thought it doesn't need to be milli-second accurate so maybe incorporating a 1 second loop would keep resource demands low. Another alternative I was going to look at is to have a While control again based on the Event Start.
And Rostov kindly posted this possible solution in another thread which I have yet to try ...
But I go back to my reason for raising this as a Suggestion. All I am suggesting is that something as basic as not carrying forward erroneous data when the Market changes should be in the software, nothing more. I am not talking of anything fancy such as automatic active trading with multiple bets and the complications that may bring … just the removal of the runner specific data when the Market changes. Runner names are cleared when the Market changes, bet references are cleared when the Market changes, bet Info is cleared when the market changes … why is the Status column not cleared when the Market changes? It's not logical, that's why I say it's a bug!rostov wrote: ↑Tue May 19, 2020 11:06 amTry this in the sheet tab.
Code: Select all
Private Sub Worksheet_Calculate() Static OldVal As Variant Application.EnableEvents = False If Range("$B$1").Value <> OldVal Then OldVal = Range("$B$1").Value Call ClearCells End If Application.EnableEvents = True End Sub
Last edited by firlandsfarm on Wed May 20, 2020 6:38 am, edited 2 times in total.
Here's a macro we have used that detects market change and clears the cells.
Code: Select all
Dim OldMarketId As String
Private Sub Worksheet_Change(ByVal Target As Range)
' cells C2:C6 will be the last range to be updated during the update process
If Target.Address = "$C$2:$C$6" Then
If Range("A1") <> OldMarketId Then
' new market, so remember market id
OldMarketId = Range("A1")
' clear the global cells
Range("L6:O6").ClearContents
' clear betting command
Range("L9,L11,L13,L15,L17,L19,L21,L23,L25,L27,L29,L31,L33,L35,L37,L39,L41,L43,L45,L47,L49,L51,L53,L55,L57,L59,L61,L63,L65,L67").ClearContents
' clear odds
Range("M9,M11,M13,M15,M17,M19,M21,M23,M25,M27,M29,M31,M33,M35,M37,M39,M41,M43,M45,M47,M49,M51,M53,M55,M57,M59,M61,M63,M65,M67").ClearContents
' clear stakes
Range("N9,N11,N13,N15,N17,N19,N21,N23,N25,N27,N29,N31,N33,N35,N37,N39,N41,N43,N45,N47,N49,N51,N53,N55,N57,N59,N61,N63,N65,N67").ClearContents
' clear status
Range("O9,O11,O13,O15,O17,O19,O21,O23,O25,O27,O29,O31,O33,O35,O37,O39,O41,O43,O45,O47,O49,O51,O53,O55,O57,O59,O61,O63,O65,O67").ClearContents
End If
End If
End Sub
- firlandsfarm
- Posts: 2688
- Joined: Sat May 03, 2014 8:20 am
BA, I was only suggesting the "O" cells are cleared. I appreciate your concerns if the "L", "M" and "N" were cleared but I'm not suggesting that … I'm simply suggesting that the "O" cells, the Bet Prevention cells, are cleared just as the "B", Selection Names, cells are cleared, the "P" - "S" Bet Reference cells are cleared and the "T" - "AE" Bet Info cells are cleared. I have not suggested the "L", "M" and "N" cells be cleared, they are not the same, they do not prevent the placing of bets on the new Market. There would be NO need for manual intervention, no wiping of formula or values in those cells. I appreciate your concern about "any betting commands left in columns L/M/N to trigger immediately when given a market" why not have it as a "do you want to clear [these] cells when the Market changes … Yes/No" so those who don't want them cleared don't have to.Bet Angel wrote: ↑Tue May 19, 2020 2:35 pmSpot on. We occasionally have requests for cell clearing into support and, when clarified, every request is different.
The idea of the green coloured cells in Excel is that they can be populated with customer's own formulae - if those cells were cleared when switching markets then all the formulae would be wiped out too. So you'd create a situation where the spreadsheet required manual intervention on each market, or required VBA code to populate it (in which case why not also use VBA to clear it exactly as required).
Some ask for just the status column to be wiped when switching markets, but that would cause any betting commands left in columns L/M/N to trigger immediately when given a market. That may suit a few people, but certainly not everyone, and you'd think that Automation Rules could do the same without even bothering with Excel for simple scenarios like that. Others ask for Status and Odds to be cleared, but not Stake. And I'm sure there must be some poeple that want Stake cleared but not Odds!
We're happy to take a look at auto-clearing if we can have a consensus as to how it should work, but we're yet to see more than a few people that want the same behaviour. Normally offering a solution like this, just moves the problem somewhere else until VBA is the only solution. Excel is wonderful with VBA, but limited without it.
- firlandsfarm
- Posts: 2688
- Joined: Sat May 03, 2014 8:20 am