Clearing the Status (and other) cells in Excel

Post Reply
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Guys, thanks for all the inputs, all different so much to try. And thank you for the various suggestions for subroutines clearing the cells but as I've said I have a sub for that and if works great if fired from the keyboard. The problem is getting it to fire from within VBA.

I will have a look in the morning at the suggestions. All I want to do is clear the old runner specific data when the worksheet switches to a new market. I don't want to make multiple bets within the same market … just one bet and move on to the next market. Why oh why does BA think I would want to keep the out of date redundant data referring to a race just gone and apply it to the next race? What has a bet placed on the 3rd runner in the 2:30 got to do with the 3rd runner in the 3:00? It's madness!

It's a problem I've always had every time I try to fire a cell clearing sub … this is my 3rd attempt at an Excel BOT and the same as the other two times it does not work (for me)! I've tried using "Sheets("Bet Angel P").Activate" but that didn't make any difference, still nothing! I've tried 3 suggested code sets from the Internet but they didn't do anything. I'm now going to try and set up a dynamic system timer and compare that with the Event Time cell to ID when that time has passed and clear the cells then but my concern is that online articles suggest that can be resource heavy … something we don't want. As I said in my last post I just can't see why BA put this complication in what they claim is automation software. All it would need is a checkbox cell asking if the user wants the Status and other race specific cells to be retained or cleared when the market changes … It's not Saturn 5 science!
rostov
Posts: 50
Joined: Mon Jul 02, 2012 3:45 pm

Try this in the sheet tab.

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
User avatar
jimibt
Posts: 3641
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

Atho55 wrote:
Mon May 18, 2020 10:35 am
Image
lol -just noticed your little edit on the red bubble on the cover: Clear status Cells the EASY way
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

jimibt wrote:
Tue May 19, 2020 3:04 pm
lol -just noticed your little edit on the red bubble on the cover: Clear status Cells the EASY way
Atho, that one passed me by!! :lol: :lol: :lol:
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

My photo editing skills obviously better than my VBA skills...
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

rostov wrote:
Tue May 19, 2020 11:06 am
Try this in the sheet tab.

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
Hi Rostov, just a note to say thanks for the code and I can confirm it's working perfectly. I can see your logic … save the Market as a variable (OldVal) then check that the current market equals OldVal. If it doesn't then the Market has changed so call the subroutine to clear the cells (or anything else you may want to do). I wanted to do it by having code to call the sub that actually clears the cells because in the future I can use that code to call any subroutine as I build a library of subs. Thanks again and sorry for the delay in acknowledging but I got distracted into another thread where I have shared your wisdom and given credit. :) Now I need to explore moving my cell formulae into VBA.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

ShaunWhite wrote:
Sun May 17, 2020 6:54 pm
... have you tried prefixing the range with a sheets("SheetName") I'm wondering if it's losing track of which is the active sheet … maybe wrap the process with ... but it's the commands that disable/enable calcs and refreshes.
sa7med wrote:
Mon May 18, 2020 9:21 am
... as shaun said if it's working when you call it manually on an activesheet then it's probably not pointing to the right place ...Why not feed your sub the worksheet that you want to clear … Another option would just be to copy the code over into the sheets themselves into your worksheet calculate event, rather than calling it.
Hi Shaun/sa7med, thanks for the suggestions. Yes I did try adding the Sheetname but it didn't make any difference and the code was already in the active worksheet and not as a Module (more by luck than judgement). Enable/disable calcs etc. is something I need to think about more but as I said to Shaun earlier I've not spent much time on housekeeping, too much to learn!

And thanks for your point about clearing cell AI7. I thought about that and came to the conclusion I can't clear it because it was an IF cell formula so to clear it would delete the formula then I realised the advantage of having VBA calculate the formula and assign the value to the cell. The cell value can then be cleared without deleting the formula. Thanks Shaun, I now realise an advantage in having VBA hold the formulae.

sa7med knows I now have the code I was looking for, I made it a Suggestion that it should be a standard feature of BA to offer the option of clearing the Status cells when the market changes (I can't think of why anyone would want to retain old bet data into a new Market). BA have now published a standard workbook that clears all cells, even the Bet Placement, Odds and Staking cells. I think this is overkill, I don't see a need to clear those cells the same as the Status cells. As the facility is likely to be more a thing for beginners like me I have posted a comment about this on that Thread and simple instruction how to change the cells to be cleared. My problem was not the clearing of the cells it was recording the Market had changed to trigger the clearing.

My thanks to you both for your input.
Anbell wrote:
Mon May 18, 2020 11:05 am
... from what I've read you should probably stay away from SELECT. It is sluggish and unnecessary … If Left(ws.Name, 9) = "Bet Angel" Then … Sheets(ws.Name).Range("B1").ClearContents … Sheets(ws.Name).Range("L9:L55") = "" … End If … Sheet6.Range("U58:V59").Value = ""
Hi Anbell, thanks for the post and sorry for the hard precis. Thanks for the tip off about Select, I'm scratch learning by researching a task on an as required basis so such little snippets point me in a direction to research just like the turning on/off calcs etc. I'm sure many will say I should pick up a book and be more structured. :) One area in your post that I noticed is that you seem to use ClearContents … Range(...) = "" … Range(...).Value = "" to do what appears to me to be the same thing … clear cells. Is there a subtle difference between them?
rostov
Posts: 50
Joined: Mon Jul 02, 2012 3:45 pm

firlandsfarm wrote:
Wed May 20, 2020 6:34 am
rostov wrote:
Tue May 19, 2020 11:06 am
Try this in the sheet tab.

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
Hi Rostov, just a note to say thanks for the code and I can confirm it's working perfectly. I can see your logic … save the Market as a variable (OldVal) then check that the current market equals OldVal. If it doesn't then the Market has changed so call the subroutine to clear the cells (or anything else you may want to do). I wanted to do it by having code to call the sub that actually clears the cells because in the future I can use that code to call any subroutine as I build a library of subs. Thanks again and sorry for the delay in acknowledging but I got distracted into another thread where I have shared your wisdom and given credit. :) Now I need to explore moving my cell formulae into VBA.
That's precisely what it does.
Post Reply

Return to “Tips and tricks”