countdown timer in a cell

[David W]

is it possible to have a countdown timer in a cell,
can it be triggered when then sheet is opened and countdown from 15secs.

or is there a way to display the time from 'ontime' that you would use to
pause an application

right now I have got a sheet that shows for 15secs., then a different sheet
shows after that. Is there way to take that countdown time and show it or
does it count up

what the end result will be is a sheet that shows for 15 sec then goes away
,during the showing of that sheet I would like for a timer to countdown to 0
in a cell so that the user will know how many seconds is left for viewing

 

[Tom Ogilvy]

Public NextTime As Date
Public EndTime As Date


Sub StartCount()
EndTime = Now + TimeValue("00:00:15")
NextTime = Now + TimeValue("00:00:01")
ActiveSheet.Range("A1").NumberFormat = "hh:mm:ss"
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End Sub

Sub Continuecount()
NextTime = Now + TimeValue("00:00:01")
If EndTime - Now < 0 Then Exit Sub
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End Sub

 

[David W]

Where do you put this?

 

[Tom Ogilvy]

In a general module.

You will have to call startcount from the code that shows the sheet.

This executes asynchronously, you can't call it and wait for it to finish.
You would put in code to hide the sheet and continue processing in the
Continuecount routine - see revised example below.

Sub Continuecount()
Dim sh as Worksheet
NextTime = Now + TimeValue("00:00:01")
If EndTime - Now < 0 Then
set sh = ActiveSheet
worksheets("something").Activate
sh.Visible = xlSheetHidden
' call code to continue process
Else
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End if
End Sub

 

[David W]

how do you pull the start count?