Timer macro for excel

[lokeshsu]

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

 

[Janusz]

hi Damon,
I looked for quite a similar code, but I am trying to crate a macro that each line has its own timer with buttons. It would be for the purpose of organising my work time, as I wanted to count time I spend on each of the action. I have tried to apply your solution but I have failed with adding buttons. Not sure how to change properties so the button would work.
When I click on button, then on design model I have StartBtn above A1 and =EMBED("Forms.ComandButton.1""") as a function Not sure if this function is correct.
Any help from you would be appreciated.
Regards,
Janusz

 

[Janusz]

hi Damon,
I looked for quite a similar code, but I am trying to crate a macro that each line has its own timer with buttons. It would be for the purpose of organising my work time, as I wanted to count time I spend on each of the action. I have tried to apply your solution but I have failed with adding buttons. Not sure how to change properties so the button would work.
When I click on button, then on design model I have StartBtn above A1 and =EMBED("Forms.ComandButton.1""") as a function Not sure if this function is correct.
Any help from you would be appreciated.
Regards,
Janusz

 

[dabl2]

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.

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).

Thanks for this code, I have it working.

If possible, I'd like a version of this where a user enters a value, such as 15:00:00 in cell B3. When the start commandbutton is pressed the timer starts counting down.

Any help appreciated

--Don

 

[dabl2]

Thanks for this code, I have it working.

If possible, I'd like a version of this where a user enters a value, such as 15:00:00 in cell B3. When the start commandbutton is pressed the timer starts counting down.

Any help appreciated

--Don

EDIT: I fooled around more with this and think I have what I need.....

 

[Tufty]

Thanks very much for this code - I find it extremely useful. There is one thing I'd like your help with though... Once I save the file and re-open it, the timer shows the time it was stopped at but when I click "Start" it starts from 00:00:00 rather than continuing from where it stopped. I should say again that this only happens when I save and close and reopen the file. How would I get it to always start at the displayed time each time I open the file unless I click reset. Hope this makes sense.

 

[james787r]

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.

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).

Hi, I tried your code but maybe I am doing something wrong.

Each time I click start button the time in the cell increase by 1 second rather than automatically increasing itself.

I was hoping to create a simple 15 minute count-down timer with you code.

Please can you help. I am happy to send over my worksheet if it helps.

Many thanks,

 

[excellent85]

Thank you so much for this code, it worked perfectly. However, when I save the workbook and come back into it, I'm having an issue. For example, if I saved the workbook at a time of 0:08:53 and then reopen it, the cell still shows my original time, but when I press start, it knocks it back to 0:00:00 and starts over. Is there any workaround for this issue?

 

[excellent85]

Thank you so much for this code, it worked perfectly. However, when I save the workbook and come back into it, I'm having an issue. For example, if I saved the workbook at a time of 0:08:53 and then reopen it, the cell still shows my original time, but when I press start, it knocks it back to 0:00:00 and starts over. Is there any workaround for this issue?

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.

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).

 

[SadieMae]

I'm having the same issue as a few others. Once file is saved and reopened, the original elapsed time is there but when I click start to resume the time it goes back to 0:00:00. Is there a way around this?

 

[jrevard]

I have been working on this with a little success. I can get the code to work, but I want it in a userform textbox. I usually use just the form code, but I am not sure how to make this work. I am getting the "increases one second every click" problem already mentioned on this thread. Any suggestions on how to integrate this into a form?