asked on

Countdown timer in Excel

Not sure if this sent properly last time,so forgive me if it gets posted twice.

I want to display a countdown timer in a cell on an excel worksheet (or it could be in a box/form) that counts down in hours, minutes and seconds. Basically, the user enters the time allowed in one cell (say 3 hours in the format 03:00:00), presses a command button, and the timer counts down in another cell. It should also display the elapsed time (in seconds) in a third cell.

So, for example, you type 03:00:00 in A1, the countdown timer displays 03:00:00, 02:59:59, 02::58:58, etc. in A2 and 01, 02, 03, etc. in A3.

It is for a game and the user must always be able to see the timer, even if they are doing something else on the sheet. Also, the third cell must be in seconds because it will be used later to deduct credits for every second they use up.

Thanks

Terry

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

WJReid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Terrygordon

ASKER

Thanks Brad - Brilliant as usual, and I particularly liked the fact that you explained which sheets to put the code in and the inclusion of the 'stop on workbook close'. Incidentally, I have used the trick/advice you gave me about 2 years ago and put the timer and elapsed seconds into autoshapes, by linking them to the cells. So basically I end up with displays that actually look like digital clocks.

Bill - Your solution was also good, and did have the actual spreadsheet, but I'm afraid it was just a little later than Brads and it didn't have the second counter (i.e. your format was 00:02:10, as opposed to 130 seconds).

However, for the sake of fairness (what's 5 minutes between friends) I am increasing the points to 350 with 250 to Brad and 100 to Bill. I am sure that other users will benefit from both solutions.

Regards

Terry

Terrygordon

ASKER

Sorry. Forgot to increase the points.

WJReid

Hi Terry,

Thank you very much for increasing the points and giving some to me, very kind of you since Brad was in first.

Bill

Terrygordon

ASKER

Brad

When I go to close the workbook I am getting the following error message:

"Procedure declaration does not match description of event or procedure having the same name"

The compiler points to the workbook sub:

Sub Workbook_BeforeClose() 'Goes in ThisWorkbook code pane
StopTimer
End Sub

which is obviously in the This Workbook code pane.

Any thoughts?

Regards

Terry

byundt

Terry,
I forgot the Cancel parameter:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Goes in ThisWorkbook code pane
StopTimer
End Sub

You may also get an error if the timer has already been stopped. The fix for this is:

Sub StopTimer() 'Goes in regular module sheet
On Error Resume Next
booOnTime = False
Application.OnTime earliesttime:=RunWhen, _
procedure:="CountdownTimer", schedule:=False
On Error GoTo 0
End Sub

Brad