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 ?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
J

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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.
 
J

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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
 
Andrew Poulsom

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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 ??
 
J

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100

ADVERTISEMENT

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>
 
J

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
apologies for the large empty space not sure why that happened

any suggestions re: my initial query ??
 
Andrew Poulsom

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 
D

daveus

Board Regular
Joined
May 18, 2004
Messages
191
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
 
You must log in or register to reply here.

Similar threads

S
  • Question
Timer to Protect a Workbook
Replies
2
Views
134
ShyD
S
O
  • Question
timer macro
2
Replies
19
Views
352
Logit
Logit
A
  • Question
Two timers alert message
2
Replies
10
Views
212
offthelip
O
T
  • Question
Auto save and close with timer
Replies
2
Views
235
Jaafar Tribak
Jaafar Tribak
G
  • Question
Web Timer
Replies
0
Views
74
gummybear
G

Watch MrExcel Video

Learn Excel - Changing Case in Excel: Lower, Upper, Proper: Podcast #1356

Forum statistics

Threads
1,122,514
Messages
5,596,604
Members
414,081
Latest member
penguin23

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
Top