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?

 

[rollis13]

Since I have no idea what the time countdown code looks like, see if you can merge this macro into your project. Since it triggers via WorkSheet_Change event, it must be pasted into the sheet's module.
In my example I'm assuming that the cell with the countdown is B1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B1")) Is Nothing Then '<- adjust range as needed
        Application.EnableEvents = False              '<- may not be needed
        Select Case Range("B1")                       '<- as above, adjust range as needed
            Case Is = 50
                Call Macro1
            Case Is = 40
                Call Macro2
            Case Is = 30
                Call Macro3
                '... ecc.
        End Select
        Application.EnableEvents = True                   '<- as above, may not be needed
    End If
End Sub

 

[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 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 i put in my countdown cell and macros etc but got a syntax error

 

[rollis13]

You missed a bracket.

 

[Erantes]

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F4")) Is Nothing Then
Application.EnableEvents = False
Select Case 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

Sorry to trouble you again but when i try and fire this code the top line gets highlighted now.

 

[rollis13]

Is the line highlighted before or after the pop-up error message?
Now you've probably misspelled (or missing) one of the macro calls.

 

[Erantes]

hi rollis13 so as it stands just now i have your script in sheet 1 and when i put the time into F4 to say "01:00:00 the 60 minute macro does not activate. I tried removing Application.EnableEvents = False and Application.EnableEvents = True but this is not working either. I dont know if its worth mentioning but the countdown clock in F4 is from a external source.

 

[rollis13]

Do you mean that it doesn't activate only when F4 is "01:00:00" and works for all the other cases or it never works ? The event macro will trigger at every change of F4 so the first input isn't considered. In this case you will have to add some code to the macro that activates the countdown to run the macro "RACE1TIME60".
As said in post #2 I have no idea what your time countdown code looks like and neither what you mean with "external source" so, if what I just said isn't manageable, it would be of much help if you attach a demo file.

 

[Erantes]

It doesn't work in any of the cases and there is code in all the macros that i have in this sheet. How would i attach a demo file.

 

[rollis13]

First of all please check if Application.EnableEvents is True (this can be done in the Immediate pane (Ctrl+G if not already visible); just paste ?Application.EnableEvents and press Enter).
When a macro with Application.EnableEvents = False is interrupted the event needed to be manually re-enable elsewise it will no longer trigger the macro (paste Application.EnableEvents = True in the Immediate pane and press Enter) then go back to your sheet and retry.
Information about attaching a demo file can be found here but in this case I think that the best thing is to use paste a link from a free hosting service. Elsewise, please paste the code of the macro (or macros) for Countdown and one of the RACE1TIMExx macros.