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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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).
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi Damon

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

Regards

Locum
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top