Macros and countdown timer

Erantes

New Member
Joined
Dec 27, 2023
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Hi I have a cell that has a time countdown clock in it which counts down to 0. On the same sheet I have macros that take info at 60 mins,40mins,30mins and so on until it reaches 0. Is it possible to execute the 60 minute macro when the timer reaches 60mins on countdown clock automatically?
 
Hi rollis13 i have done the check you mentioned and it is indeed showing "True". There isn't a macro or code for the the timer only the RACE1TIMEXX macro. Is it possible to sent a copy to you by email?
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Is it a problem for you to paste the RACE1TIME macro code here? Either way, it may not be necessary if you don't first explain how the countdown is done since you say, "There is no macro or code for the timer." I can't understand how it could be done and it is what triggers the macro I suggested.
 
Upvote 0
Sub RACE1TIME60()
'
' RACE1TIME60 Macro
'

'
ActiveWindow.ScrollColumn = 214
ActiveWindow.ScrollColumn = 211
ActiveWindow.ScrollColumn = 183
ActiveWindow.ScrollColumn = 161
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 3
Range("K9:K48").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll ToRight:=8
ActiveWindow.SmallScroll ToRight:=16
Range("HN9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 219
ActiveWindow.ScrollColumn = 218
ActiveWindow.ScrollColumn = 217
ActiveWindow.ScrollColumn = 216
ActiveWindow.ScrollColumn = 214
ActiveWindow.ScrollColumn = 212
ActiveWindow.ScrollColumn = 211
ActiveWindow.ScrollColumn = 207
ActiveWindow.ScrollColumn = 196
ActiveWindow.ScrollColumn = 174
ActiveWindow.ScrollColumn = 154
ActiveWindow.ScrollColumn = 115
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 3
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll ToRight:=8
ActiveWindow.SmallScroll ToRight:=16
Range("HP3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

The excel workbook that i am using is provided by betangel and it has 10 sheets on it that you can load 10 races and one of the bits of info they provide is the countdown timer to the race start. So its not a macro as more a preloaded bit of information.
 
Upvote 0
The RACE1TIME macros only do some Copy/Paste so they are not of any impediment but the Application.EnableEvents False/True must be in my macro.
But, since I have no idea how Betangel works, and since you can't find a specific countdown macro, I think it's likely a popup. If so there's no way to detect its contents and trigger the Worksheet_Change event in my macro.
But more, in post #3 you referenced cell F4 as countdown cell so please check again for a macro. Elsewise I can't be of any other help.

Ps. maybe it's some sort of Power Query.
 
Last edited:
Upvote 0
Southwell 9th Jan - 17:00 6f App Hcap
Total matched£232,418.30Tx Count0
Last Updated17:01:24Event Start17:00:00
Number of Runners10Countdown00:40:00
Unmatched Bets Count0
Balance£0.00
Runner namesP & LClose Trade P & LBackLayLast Traded Price
Green Up P & LRunner Volume
Mumayaz0.000.004.84.955.15.25.32
0.00£262.08£190.70£121.99£89.86£213.85£121.51£67,182.05
Mokaatil0.000.005.85.966.26.46.62
0.00£217.90£199.11£61.58£231.89£84.94£247.32£41,463.91
Mr Squires0.000.008.28.48.68.899.22
0.00£60.64£94.97£19.65£35.00£22.22£35.71£31,328.48
Kats Bob0.000.0077.27.47.67.882
0.00£119.26£181.88£93.91£13.80£101.30£61.84£21,204.10
Captain Vallo0.000.005.966.26.46.66.82
0.00£116.59£107.07£70.61£80.47£66.34£83.33£23,266.00
Starsong0.000.008.68.899.29.49.69.2
0.00£98.89£74.86£15.85£33.32£35.44£58.06£14,732.62
El Hombre0.000.001717.51818.51919.518
0.00£45.24£97.39£26.82£20.41£41.28£68.73£11,959.59
Sparkle In His Eye0.000.0030323438404236
0.00£37.71£41.09£19.85£11.36£24.55£50.25£9,973.55
Princess Naomi0.000.0030323436384036
0.00£137.57£28.54£7.79£15.19£37.46£17.91£7,644.13
Canaria Queen0.000.008085909510011090
0.00£8.67£18.69£9.91£5.34£23.59£21.81£3,663.87
Brucie Bonus0.000.0020027050010001000
0.00£25.00£9.99£16.47£0.99£0.02

This is one of the sheets were i take all my information from and then put into my own table. You connect betangel to a excel workbook and you select the races you are interested in.
 
Upvote 0
You need to understand and possibly explain here how the data in that sheet is updated.
To check my macro you could try manually updating the F4 cell and this will definitely trigger my macro (I tested it before publishing and it works without any issues). So you'll have to figure out on your own how to trigger it with the external update of that cell.
 
Upvote 0
Hi rollis13 I worked out it was to do with the time format in the countdown cell so thought I had to put say "01:00:00" into a figure eg 60. In a new cell I put ("01:00:00 × 1440) which would give me my 60 figure. So used this cell in worksheet change, it still didn't work. The weird thing is if I manually put 60 in this cell the macro fires perfectly, any ideas!
 
Upvote 0
Since your cell F4 is probably formatted date (time) use:
VBA Code:
Select Case CDate(Range("F4"))

or

Select Case CDate(Range("F4").Value)
 
Upvote 1
Solution
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F4")) Is Nothing Then
Application.EnableEvents = False
Select Case CDate(Range("F4"))
Case Is = "01:00:00"
Call RACE1TIME60
Case Is = "00:50:00"
Call RACE1TIME50
Case Is = "00:40:00"
Call RACE1TIME40
Case Is = "00:30:00"
Call RACE1TIME30
Case Is = "00:20:00"
Call RACE1TIME20
Case Is = "00:15:00"
Call RACE1TIME15
Case Is = "00:10:00"
Call RACE1TIME10
Case Is = "00:05:00"
Call RACE1TIME5
Case Is = "00:03:00"
Call RACE1TIME3
Case Is = "00:02:00"
Call RACE1TIME2
Case Is = "00:01:00"
Call RACE1TIME1
End Select
Application.EnableEvents = True
End If
End Sub

Hi rollis13 i would like to thank you so much as this has my sheet working perfectly now. You have saved me so much time and angst so once again thank you .
 
Upvote 0
In post #15 you said it was one of ten sheets so if they all need to be updated at the same time your macro, this time to pasted only in the "ThisWorkBook" module (and not in all the sheets module), could be changed to the below code. Not sure if the countdown timing will mess up when you have macros start a second apart.
VBA Code:
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) '<- changed
    If Not Intersect(Target, Sh.Range("F4")) Is Nothing Then '<- changed
        Application.EnableEvents = False
        Select Case CDate(Sh.Range("F4"))              '<- changed
            Case Is = "01:00:00"
                Call RACE1TIME60
            Case Is = "00:50:00"
                Call RACE1TIME50
    '... and so on
Then, just to say, the other macros could be cut down to (example for RACE1TIME60 since there is a lot of redundant coding coming from the Macro Recorder):
Code:
Sub RACE1TIME60()
    Range("K9:K48").Copy
    Range("HM9").PasteSpecial Paste:=xlPasteValues
    Range("C2").Copy
    Range("HP3").PasteSpecial Paste:=xlPasteValues
End Sub
Anyway, thanks for the positive feedback(y), glad having been of some help.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,595
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top