Countdown Timer

[uniden32]

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

 

[VoG]

This should help http://www.mrexcel.com/forum/showthread.php?t=433092

 

[uniden32]

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,

 

[VoG]

See if this does what you want

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

 

[uniden32]

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

 

[VoG]

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

 

[uniden32]

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.

 

[VoG]

Maybe something like

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

 

[uniden32]

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

 

[uniden32]

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.