Timer macro for excel

lokeshsu

Board Regular
Joined
Mar 11, 2010
Messages
178
Hi all,

I need a macro where when click of the button it should start the timer and counting increases like from 00:00:00 to 00:00:01 and further, sec by sec the time should increase and on press of the button the timer should stop

Thanks in advance
Lokesh
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hello Lokesh,

Here's some code that implements a timer. You didn't mention where you want the time displayed--this code does it in a cell, but it is just as easy to modify the code to display it in the Excel Status Bar, in a userform textbox, or in a drawing or shape object.

I implemented this assuming you would like a Start button but also to have a Stop button and a Reset button, and that you would like to be able to stop and restart the timer.

Code:
Dim StopTimer           As Boolean
Dim SchdTime            As Date
Dim Etime               As Date
Const OneSec            As Date = 1 / 86400#

Private Sub ResetBtn_Click()
    StopTimer = True
    Etime = 0
    [B3].Value = "00:00:00"
End Sub

Private Sub StartBtn_Click()
   StopTimer = False
   SchdTime = Now()
   [B3].Value = Format(Etime, "hh:mm:ss")
   Application.OnTime SchdTime + OneSec, "Sheet1.NextTick"
End Sub

Private Sub StopBtn_Click()
    StopTimer = True
    Beep
End Sub

Sub NextTick()
   If StopTimer Then
      'Don't reschedule update
   Else
      [B3].Value = Format(Etime, "hh:mm:ss")
      SchdTime = SchdTime + OneSec
      Application.OnTime SchdTime, "Sheet1.NextTick"
      Etime = Etime + OneSec
   End If
End Sub

This code must be placed in the sheet's code module. To do this, right-click on the sheet's tab, select View Code, and paste this code into the Code pane. Note that I assumed the sheet is Sheet1 and you should edit this name (2 places in the code). This is the sheet's codename, not the tab name. The codename is the name that appears between the sheet icon and the tab name in parentheses in the Visual Basic Editor Project pane.

This code assumes you have a start commandbutton named StartBtn, a stop button named StopBtn, and a reset button named ResetBtn (only the start button is required if you don't have a need to stop or reset the timer). To name a button simply select it and type the new name in the Name box just above cell A1.

This code displays the timer time value in cell B3, but you can change this by editing the code (3 places).
 

lokeshsu

Board Regular
Joined
Mar 11, 2010
Messages
178
Thanks You very much Damon Ostrander, the code is working and i needed the exact code. Thank you very much



Hello Lokesh,

Here's some code that implements a timer. You didn't mention where you want the time displayed--this code does it in a cell, but it is just as easy to modify the code to display it in the Excel Status Bar, in a userform textbox, or in a drawing or shape object.

I implemented this assuming you would like a Start button but also to have a Stop button and a Reset button, and that you would like to be able to stop and restart the timer.

Code:
Dim StopTimer           As Boolean
Dim SchdTime            As Date
Dim Etime               As Date
Const OneSec            As Date = 1 / 86400#

Private Sub ResetBtn_Click()
    StopTimer = True
    Etime = 0
    [B3].Value = "00:00:00"
End Sub

Private Sub StartBtn_Click()
   StopTimer = False
   SchdTime = Now()
   [B3].Value = Format(Etime, "hh:mm:ss")
   Application.OnTime SchdTime + OneSec, "Sheet1.NextTick"
End Sub

Private Sub StopBtn_Click()
    StopTimer = True
    Beep
End Sub

Sub NextTick()
   If StopTimer Then
      'Don't reschedule update
   Else
      [B3].Value = Format(Etime, "hh:mm:ss")
      SchdTime = SchdTime + OneSec
      Application.OnTime SchdTime, "Sheet1.NextTick"
      Etime = Etime + OneSec
   End If
End Sub

This code must be placed in the sheet's code module. To do this, right-click on the sheet's tab, select View Code, and paste this code into the Code pane. Note that I assumed the sheet is Sheet1 and you should edit this name (2 places in the code). This is the sheet's codename, not the tab name. The codename is the name that appears between the sheet icon and the tab name in parentheses in the Visual Basic Editor Project pane.

This code assumes you have a start commandbutton named StartBtn, a stop button named StopBtn, and a reset button named ResetBtn (only the start button is required if you don't have a need to stop or reset the timer). To name a button simply select it and type the new name in the Name box just above cell A1.

This code displays the timer time value in cell B3, but you can change this by editing the code (3 places).
 

Old Dog

New Member
Joined
Sep 30, 2013
Messages
2
Hello Lokesh,

"I implemented this assuming you would like a Start button but also to have a Stop button and a Reset button, and that you would like to be able to stop and restart the timer."

Blah blah blah

"This code assumes you have a start commandbutton named StartBtn, a stop button named StopBtn, and a reset button named ResetBtn (only the start button is required if you don't have a need to stop or reset the timer). To name a button simply select it and type the new name in the Name box just above cell A1."

This code displays the timer time value in cell B3, but you can change this by editing the code (3 places).

All right, I have a question about the buttons: (I have Excel 2010). I do not see any "buttons" anywhere so I cannot "select" it. So, where are the buttons????

I will also have other questions about this: please note, I am not a programmer but I can follow logic unlike my wife......

I really need a timer bad. When I pasted the code into the module and then hit run, it advanced the time by 1/100 of a second each time I hit "Run". So, it is not cycling continuously. I suspect getting the Start, Stop, and Reset buttons up and running may solve the problem.

I need your help, please.
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239

ADVERTISEMENT

Hi Old Dog,

You must create the buttons yourself, getting them from the ActiveX controls menu. If you have Excel 2007 or later this menu is accessed from the Developer tab in the ribbon, then using the Insert button of the Controls group to drop down the ActiveX controls menu. Don't forget to name them according to my instructions using the Properties button in the Controls group to bring up each button's editable properties list. If you have trouble with this let me know and I'll provide more direct help.
 

Old Dog

New Member
Joined
Sep 30, 2013
Messages
2
More direct help needed, please.

No successful with the buttons. Did use Active-X this time but didn't seem to help.
I am not sure that I am naming the buttons correctly (need help with this)
I did insert the code into the sheets code module.
It is sheet 1 (I have not changed the name.
Thank you for getting back to me.
OD
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239

ADVERTISEMENT

Hi again Old Dog,

Each button's Name property is at the very top of the its Properties list.

I'm going to send you my email address via Private Message. If you would like you could email me your worksheet and I'll get it working.

Damon
 

Locum

New Member
Joined
Jan 7, 2014
Messages
2
Hi Damon

I have followed your instructions above and it all works, save that the StopBtn doesn't actually stop the timer. I have double checked all the steps, including the Properties of the StopBtn, but can't get it to work. The other two buttons work fine. Do you have any ideas as to why this might be? Your help would be much appreciated.

Locum
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Locum,

There are only a few reasons I can imagine for the Stop button not working.

1. The stop button not actually linked to the StopBtn_Click event. This could happen if there is an error (e.g., a typo) in naming the button "StopBtn".

2. The Dim statement (Dim StopTimer As Boolean) at the top of the code module is missing, or the StopTimer variable is not consistent with the variable name in the code. I suggest you add

Option Explicit

at the top of the module to ensure all the variables are correctly named and consistent.

3. The Enabled property of the StopBtn accidentally got set to False.

That's all I can think of. Let me know if none of these suggestions work and I'll PM you my email and you can send me your workbook.

Damon
 

Locum

New Member
Joined
Jan 7, 2014
Messages
2
Hi Damon

Thanks for the reply. It was 2. - the Dim statement. Poor cut and paste skills on my part :oops:

Regards

Locum
 

Watch MrExcel Video

Forum statistics

Threads
1,114,551
Messages
5,548,699
Members
410,866
Latest member
StuartAllison
Top