timer

J

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
I have already made a stopwatch function where I can click start and click stop and get the time difference accurate to one-one thusandth of a second, but I need a single cell ( let's say E1 ) to display the continuously changing elapsed time from when I click on my Start button.

any ideas ?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
ok I found the following code that places the running time elapsed into a cell ....

Public stopMe As Boolean
Public resetMe As Boolean
Public myVal As Variant
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
If Target.Value = myVal And Target.Value <> "" Then
'Changed
Dim startTime, finishTime, totalTime, timeRow
startTime = Timer
stopMe = False
resetMe = False
myTime = Target.Offset(, 2).Value
Target.Offset(, 1).Select
startMe:
DoEvents
timeRow = Target.Row
finishTime = Timer
totalTime = finishTime - startTime
Target.Offset(, 1).Value = Format(myTime + totalTime, "0.0000") & " Seconds"
If resetMe = True Then
Target.Offset(, 1).Value = 0
Target.Offset(, 2).Value = 0
stopMe = True
End If
If Not stopMe = True Then
Target.Offset(, 2).Value = totalTime
Goto startMe
End If
Cancel = True
End
Else
'Not Changed
stopMe = True
Cancel = True
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myVal = Target.Value
End Sub

This code provides two running clocks, one of them acts as a lap timer. How can I adapt this code to
1) only have the one running timer (ie: I don't want the lap-timer function )
and
2) activate not on a double click of a particular cell but activate when a letter is placed into, say, cell A1.
 
Upvote 0
Thanks Andrew, I went through your reference but I had little success in making any of them operate.

Are the alterations to the lengthy code I gave earlier a hugely difficult task ?

I only want the one clock, and I want it top activate when a letter is placed in A1 and not activated by a double click
 
Upvote 0
Please post the code for your Start and Stop buttons.

You can use the Worksheet_Change event procedure to start the clock when A1 changes.
 
Upvote 0
The code I have in the sheet module is ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
If Target.Row < 5 Then Exit Sub
Target.Offset(0, 1) = [Now()]
End Sub

I have a start button that I have made a macro for using Tools Macro where on pressin it a 'y' is placed in A5 and the time appears in B5.

I have a stop button that I have made a macro for using Tools Macro where on pressin it a 'y' is placed in A6 and the time appears in B6.

In D5 I then have the difference between these two times.

I would like the same start button to also activate the 'running' clock that would constantly update itself every 0.1 seconds. I will need to make a separate stop button that stops and resets the 'running' clock.

Is this possible ??
 
Upvote 0
Here is a view of what I am doing .. this is my first attempt to display something so I hope this works ...



<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2">
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>y</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>14:17.110</nobr></font></td>
<td rowspan="2" align="center" valign="middle"><font face="Tahoma" size="2" color="#0000FF"><nobr>Swimmer A</nobr></font></td>
<td rowspan="2" align="center" valign="middle"><font face="Tahoma" size="2" color="#FF0000"><nobr>3.080</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>y</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>14:20.190</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td rowspan="2" align="center" valign="middle"><font face="Tahoma" size="2" color="#0000FF"><nobr>Swimmer B</nobr></font></td>
<td rowspan="2" align="center" valign="middle"><font face="Tahoma" size="2" color="#FF0000"><nobr>4.210</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>y</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>14:21.320</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td rowspan="2" align="center" valign="middle"><font face="Tahoma" size="2" color="#0000FF"><nobr>Swimmer C</nobr></font></td>
<td rowspan="2" align="center" valign="middle"><font face="Tahoma" size="2" color="#FF0000"><nobr>5.060</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>y</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>14:22.170</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td rowspan="2" align="center" valign="middle"><font face="Tahoma" size="2" color="#0000FF"><nobr>Swimmer D</nobr></font></td>
<td rowspan="2" align="center" valign="middle"><font face="Tahoma" size="2" color="#FF0000"><nobr>5.920</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>y</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr>14:23.030</nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
<tr>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
<td align="center" valign="middle"><font face="Tahoma" size="2"><nobr></nobr></font></td>
</tr>
</table>
 
Upvote 0
apologies for the large empty space not sure why that happened

any suggestions re: my initial query ??
 
Upvote 0
Here is some code that you can assign to a couple of buttons from the Forms Toolbar:

Code: 
Public RunWhen As Double

Sub UpdateClock()
'   *** Change Sheet name and Range reference to suit ***
    With Worksheets("Sheet1").Range("E1")
        .NumberFormat = "hh:mm:ss"
        .Value = Now
    End With
    RunWhen = Now + TimeSerial(0, 0, 1)
    Application.OnTime RunWhen, "UpdateClock"
End Sub

Sub StopClock()
    Worksheets("Sheet1").Range("E1").ClearContents
    Application.OnTime EarliestTime:=RunWhen, Procedure:="UpdateClock", Schedule:=False
End Sub
 
Upvote 0
jammoca, is your timer really accurate to 0.000? you may have the digits, but do they every show accuracy that far out? I can't get my timer to be more accurate than seconds. Didn't know if excel could do more accurate than one second.

If it can, please let me know how to get more accurate than one second. I changed the format, but still only get seconds accuracy, not tenths or 100ths. Thoughts?

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Three-lane Stopwatch?
Replies
7
Views
161
RaymondW
R
D
  • Question
Pause stopwatch
Replies
8
Views
299
Damian37
D
MsDeth
  • Question
Limit timer-running macro to one workbook, regardless of how many workbooks are open
Replies
6
Views
512
Jaafar Tribak
Jaafar Tribak
S
  • Solved
Timer
2
Replies
14
Views
1K
sspatriots
S
C
  • Question
VBA button click from within a macro
Replies
2
Views
495
Chris_yorks
C

Forum statistics

Threads
1,214,810
Messages
6,121,690
Members
449,048
Latest member
81jamesacct

Share this page

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