Countdown Timer

U

uniden32

New Member
Joined
Sep 19, 2010
Messages
22
Hey mates,

I've searched the forums for existing countdown timers, but haven't found any that suited my needs.

I'm looking for a timer that counts down the minutes and seconds based on the current time.

The user would open the spreadsheet, there would be a button they could press that would display a timer based on how many minutes were left in the current half-hour.

(ie; If it were currently 9:15.20, the timer would start at 15.20 and count down. If the time was 9:36.18, the timer would start at 23.42 and count down.)

Once the timer hit 0, it would make a sound, then it would immediately restart at 30.00, and the cycle would continue as long as the the spreadsheet was open.

Thanks in advance,

Ralph
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
I've looked at that thread, and have gotten one of the basic timers to work, but haven't figure out how to get it to cycle automatically based on the 1/2 hour requirement that I need.

Thanks in advance,
 
Upvote 0
See if this does what you want

Code: 
Dim NextTick As Date

Sub StartTimer()
With ThisWorkbook.Sheets("Sheet1").Range("A1")
    .NumberFormat = "mm:ss"
    .Value = Time
End With
Call TickTock
End Sub

Private Sub TickTock()
With ThisWorkbook.Sheets("Sheet1").Range("A1")
    .Value = .Value - (1 / 86400)
    If .Value <= (1 / 86400) Then
        .Value = Time
    End If
End With
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub

Sub StopClock()
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
On Error GoTo 0
End Sub
 
Upvote 0
Ok, I put that in the VB in my spreadsheet called Timer.xls under Sheet1 as well as ThisWorkbook as I wasn't certain ...

I run it, and I get the error message:

The macro "Y:\timer.xls'!TickTock' cannot be found

Thanks again,

Ralph
 
Upvote 0
It should go in a regular module. Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste in the code.
 
Upvote 0
VoG,

Thanks again for your replies.

I've gotten the module to run, and I've figured out how to assign buttons to the Start / Stop features.

My next step is to have it figure out when to count down from top of the hour, and when to count down from the 1/2 hour point.

So ... basically if the minutes =< 29, it would count down 30, minus whatever minutes/seconds were remaining.

if the minutes were >= 30, it would count down 60, minus whatever minutes/seconds were remaining.

I hope I'm wording this correctly, and again thanks for any and all input.
 
Upvote 0
Maybe something like

Code: 
Dim NextTick As Date

Sub StartTimer()
With ThisWorkbook.Sheets("Sheet1").Range("A1")
    .NumberFormat = "mm:ss"
    .Value = WorksheetFunction.Ceiling(Time, 1 / 48)
End With
Call TickTock
End Sub

Private Sub TickTock()
With ThisWorkbook.Sheets("Sheet1").Range("A1")
    .Value = .Value - (1 / 86400)
    If .Value <= (1 / 86400) Then
        .Value = WorksheetFunction.Ceiling(Time, 1 / 48)
    End If
End With
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub

Sub StopClock()
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
On Error GoTo 0
End Sub
 
Upvote 0
Tried the new code, but as I can see, it will just count down from 30 minutes.

What I need, is for it to automatically determine if it's closer to 1/2 past the hour or to top of the hour, then countdown.

If the time is 16:03, the timer would countdown from 27 minutes.
If the time is 12:14, the timer would countdown from 16 minutes.
If the time is 23:28, the timer would countdown from 2 minutes.

Basically, in the above scenarios, it would be counting down the remaining time to reach the next 1/2 past the hour. (ie; 12:30, 1:30, 2:30, 3:30, etc.)

If the time is 16:35, the timer would countdown from 25 minutes.
If the time is 16:41, the timer would countdown from 19 minutes.
If the time is 16:52, the timer would countdown from 8 minutes.

Basically, in the above scenarios, it would be counting down the remaining time to reach the top of the next hour. (ie; 12:00, 1:00, 2:00, 3:00, etc.)

The timer would never be more than 30 minutes, as if it were 12:31, it would coundown 29 minutes to 13:00, and if it were 12:01, it would countdown 29 minutes to 12:30.

As alway, I can't thank you guys enough for any help.

Ralph
 
Upvote 0
Ok, think I got it figured out:

Here is what I have, if anyone has any suggestions to improve, I would gladly appreciate, note that anything in **text** is not in the script, just added for this post to help explain

Dim NextTick As Date

Sub StartTimer()
With ThisWorkbook.Sheets("Data").Range("D2")
.NumberFormat = "hh:mm:ss"
.Value = Time
End With
Call TickTock1
Call TickTock
End Sub

Private Sub TickTock1()
With ThisWorkbook.Sheets("Sheet1").Range("B2")
.NumberFormat = "mm:ss"
.Value = ThisWorkbook.Sheets("Data").Range("E3") ** I use E3 to give me the value I want to count down from **
End With
End Sub

Private Sub TickTock()
With ThisWorkbook.Sheets("Sheet1").Range("B2") ** This is where I do the actual count down, using the data from E3 **
.NumberFormat = "mm:ss"
.Value = ThisWorkbook.Sheets("Sheet1").Range("B2")
.Value = .Value - (1 / 86400)
If .Value <= (1 / 86400) Then
.Value = Time
End If
End With
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub

Sub StopClock()
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
On Error GoTo 0
End Sub


Thanks in advance,

Ralph

Thanks yet again to VoG for all your assistance.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Macros and countdown timer
2
Replies
19
Views
1K
rollis13
rollis13
m_in_spain
  • Solved
Countdown timer in Userform
Replies
3
Views
389
m_in_spain
m_in_spain
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
108
BenW71
B
E
  • Question Question
Auto close spreadsheet timer not working correctly
2
Replies
17
Views
1K
eli_m
E
P
  • Question Question
Repeating Countdown timer
Replies
1
Views
859
BSALV
BSALV

Forum statistics

Threads
1,224,504
Messages
6,179,144
Members
452,891
Latest member
JUSTOUTOFMYREACH

Share this page

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