Macros and countdown timer

[Erantes]

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?

 

[Erantes]

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?

 

[rollis13]

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.

 

[Erantes]

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.

 

[rollis13]

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.

 

[Erantes]

Southwell 9th Jan - 17:00 6f App Hcap
Total matched	£232,418.30		Tx Count	0
Last Updated	17:01:24		Event Start	17:00:00
Number of Runners	10		Countdown	00:40:00
Unmatched Bets Count	0
Balance	£0.00
Runner names	P & L	Close Trade P & L			Back	Lay			Last Traded Price
	Green Up P & L							Runner Volume
Mumayaz	0.00	0.00	4.8	4.9	5	5.1	5.2	5.3	2
	0.00	£262.08	£190.70	£121.99	£89.86	£213.85	£121.51	£67,182.05
Mokaatil	0.00	0.00	5.8	5.9	6	6.2	6.4	6.6	2
	0.00	£217.90	£199.11	£61.58	£231.89	£84.94	£247.32	£41,463.91
Mr Squires	0.00	0.00	8.2	8.4	8.6	8.8	9	9.2	2
	0.00	£60.64	£94.97	£19.65	£35.00	£22.22	£35.71	£31,328.48
Kats Bob	0.00	0.00	7	7.2	7.4	7.6	7.8	8	2
	0.00	£119.26	£181.88	£93.91	£13.80	£101.30	£61.84	£21,204.10
Captain Vallo	0.00	0.00	5.9	6	6.2	6.4	6.6	6.8	2
	0.00	£116.59	£107.07	£70.61	£80.47	£66.34	£83.33	£23,266.00
Starsong	0.00	0.00	8.6	8.8	9	9.2	9.4	9.6	9.2
	0.00	£98.89	£74.86	£15.85	£33.32	£35.44	£58.06	£14,732.62
El Hombre	0.00	0.00	17	17.5	18	18.5	19	19.5	18
	0.00	£45.24	£97.39	£26.82	£20.41	£41.28	£68.73	£11,959.59
Sparkle In His Eye	0.00	0.00	30	32	34	38	40	42	36
	0.00	£37.71	£41.09	£19.85	£11.36	£24.55	£50.25	£9,973.55
Princess Naomi	0.00	0.00	30	32	34	36	38	40	36
	0.00	£137.57	£28.54	£7.79	£15.19	£37.46	£17.91	£7,644.13
Canaria Queen	0.00	0.00	80	85	90	95	100	110	90
	0.00	£8.67	£18.69	£9.91	£5.34	£23.59	£21.81	£3,663.87
Brucie Bonus	0.00	0.00	200	270	500	1000			1000
	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.

 

[rollis13]

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.

 

[Erantes]

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!

 

[rollis13]

Since your cell F4 is probably formatted date (time) use:

Select Case CDate(Range("F4"))

or

Select Case CDate(Range("F4").Value)

 

[Erantes]

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 .

 

[rollis13]

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.

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):

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, glad having been of some help.