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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Is the line highlighted before or after the pop-up error message?
Now you've probably misspelled (or missing) one of the macro calls.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,567
Messages
6,179,571
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