Count Down Timer

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have a request to program one cell of a spreadsheet that
the user can type a minute value (i.e. 00:10:00) and when
they press the enter key (with focus on this cell) a timer
starts and the display changes to count down to 00:00:00.
The value will be entered in minutes, so if there is any
way that the user just has to enter "10" instead of the
entire 8 character time format, that would be ideal.

Can anyone help with this?
 
I'd like to know how you would do that to begin with? Can you show how
(even though it's in the 8 character format)...
 
An example of how it can be done

on worksheet module place this code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address <> "$A$1" Then
Exit Sub
End If
If IsEmpty(Target) Then
Range("c1").ClearContents
Exit Sub
ElseIf Not IsNumeric(Target) Then
Range("c1").ClearContents
Exit Sub
ElseIf Target.Value < 1 Then
Range("c1").ClearContents
Exit Sub
End If
Range("c1").Value = 0
Application.OnTime Now() + TimeValue("00:01:00"), "MyTimer"
End Sub



On a normal module sheet place this code

Sub MyTimer()
If Range("c1").Value > Range("a1").Value - 1 Then
Range("c1").Value = "Times Up"
End
End If
Range("c1").Value = Range("c1").Value + 1
Application.OnTime Now() + TimeValue("00:01:00"), "MyTimer"
End Su
 
I must be missing something here. All I seem to get in
cell C1 is '0', and there is no hint of a count down timer.
 
I have a request to program one cell of a spreadsheet that
the user can type a minute value (i.e. 00:10:00) and when
they press the enter key (with focus on this cell) a timer
starts and the display changes to count down to 00:00:00.
The value will be entered in minutes, so if there is any
way that the user just has to enter "10" instead of the
entire 8 character time format, that would be ideal.
Click to expand...

Another way. The following code goes into the code module for the worksheet
whose A1 cell is the countdown timer. Both the Change and the CountDown
procedure go into the same module. If the user wants to abort the countdown, all
s/he has to do is clear the A1/countdown timer cell's contents or enter anything
other than a positive number.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim t As Double

If Target.Address(0, 0) <> "A1" Then Exit Sub

On Error Resume Next
t = Target.Value2
On Error GoTo 0

If t > 0 Then Me.CountDown

End Sub


Sub CountDown()
Static repcall As Boolean, nf As String, t As Date
Dim mer As Range, merv As Double

Set mer = Me.Range("A1")

On Error Resume Next
merv = mer.Value2

On Error GoTo CleanUp
Application.EnableEvents = False

If Not repcall Then
'Debug.Print "begun at", Now
repcall = True
nf = mer.NumberFormat
t = Now + TimeSerial(0, merv, 0)
mer.NumberFormat = "mm:ss"
mer.Value = TimeSerial(0, merv, 0)

ElseIf t > Now And merv > 0 Then
mer.Value = t - Now

Else
repcall = False
If merv > 0 Then mer.Value = "Time's up!"
mer.NumberFormat = nf
'Debug.Print "done at", Now

End If

If repcall Then Application.OnTime _
EarliestTime:=Now + TimeSerial(0, 0, 1), _
Procedure:=Me.Name & ".CountDown"

CleanUp:
Application.EnableEvents = True

End Sub
 
That works great

RAC

Harlan said:
...



Another way. The following code goes into the code module for the worksheet
whose A1 cell is the countdown timer. Both the Change and the CountDown
procedure go into the same module. If the user wants to abort the countdown, all
s/he has to do is clear the A1/countdown timer cell's contents or enter anything
other than a positive number.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim t As Double

If Target.Address(0, 0) <> "A1" Then Exit Sub

On Error Resume Next
t = Target.Value2
On Error GoTo 0

If t > 0 Then Me.CountDown

End Sub


Sub CountDown()
Static repcall As Boolean, nf As String, t As Date
Dim mer As Range, merv As Double

Set mer = Me.Range("A1")

On Error Resume Next
merv = mer.Value2

On Error GoTo CleanUp
Application.EnableEvents = False

If Not repcall Then
'Debug.Print "begun at", Now
repcall = True
nf = mer.NumberFormat
t = Now + TimeSerial(0, merv, 0)
mer.NumberFormat = "mm:ss"
mer.Value = TimeSerial(0, merv, 0)

ElseIf t > Now And merv > 0 Then
mer.Value = t - Now

Else
repcall = False
If merv > 0 Then mer.Value = "Time's up!"
mer.NumberFormat = nf
'Debug.Print "done at", Now

End If

If repcall Then Application.OnTime _
EarliestTime:=Now + TimeSerial(0, 0, 1), _
Procedure:=Me.Name & ".CountDown"

CleanUp:
Application.EnableEvents = True

End Sub
Click to expand...
 
I just tried Harlan's code (just to see what it does, I have no real use
for it...yet.) but I noticed a couple of things.

When I enter a whole number, such as 1, or 5, it works fine. Count's
down, then says "Times Up!". But, it has no provisions to *display*
anything higher than 60 minutes (61 displays 1:00, then counts down and
displays 59:59, etc) and anything that isn't just a whole number (in
minutes) gets errored out (so to speak.). You can't for instance, enter
1:00:00, for 1 hour, or 0:05 for 5 seconds, or .5 for 30 seconds (.5
being half a minute).

Just wondering if there are any ways around this. Don't get me wrong, I
think it's awesome to see a little countdown in cell A1, just wondering
if it can become more... better.

As far as I can tell, the hour display bit can be overcome by changing
time display to hh:mm:ss in Mer.Numberformat. That's at least what my
limited knowledge tells me.

Anything I missed?

-Bob
 
...
...
When I enter a whole number, such as 1, or 5, it works fine. Count's
down, then says "Times Up!". But, it has no provisions to *display*
anything higher than 60 minutes (61 displays 1:00, then counts down and
displays 59:59, etc) and anything that isn't just a whole number (in
minutes) gets errored out (so to speak.). You can't for instance, enter
1:00:00, for 1 hour, or 0:05 for 5 seconds, or .5 for 30 seconds (.5
being half a minute).

Just wondering if there are any ways around this. Don't get me wrong, I
think it's awesome to see a little countdown in cell A1, just wondering
if it can become more... better.
Click to expand...
...

Personally, I'd never use anything like this. But . . .

The CountDown procedure could be modified to use [mm]:ss or [hh]:mm:ss number
format during countdown. That'd address times > 59 minutes 59 seconds.

As for handling noninteger entries, if integers were assumed to be minutes, then
change the 'mer.Value = Timeserial(0, merv, 0)' statement to

mer.Value = CDbl(merv / 1440)

As for allowing entry of times rather than numbers of minutes, I was following
the OP's specs, in which he mentioned a preference for this. If you want to
enter time values, skip the number formatting in the CountDown procedure and
format the cell as Time.
 
Back
Top