Stopwatch

[Baron808]

Hi,

I am a new user and hope you guys can help me with my Excel problem.

I have inserted a stopwatch into my spreadsheet from an excellent article I found on this site.

However, I have a couple of problems.....

Firstly, whenever I change another cell in a different worksheet in the same book, the stopwatch stops working. I need the stopwatch to carry on as I simultaneously change cells in other worksheets.

Also, is it possible to create a total stopwatch timer no matter how many times I stop & start the stopwatch?

Many thanks!

[Bob Phillips]

Post the code, we are not mind readers.

[Baron808]

Sorry! This is the code:

[VBA]
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
[/VBA]
Thanks!

[TomSchreiner]

Hi Baraon. What precision are you looking for? Is seconds enough? I have some ready code, but the precision is in milliseconds. I suppose it would be easy enough to adapt to seconds. A doevents loop as above might not be a good choice...

[Bob Phillips]

This may be pretty cute, but it is darn inefficient, hogging the processor as it does.

If you don't need that level of granularity, that is one second intervals being good enough, you could do better using OnTime.

[Baron808]

I actually only need one minute intervals. Currently, I am just converting the seconds on stopwatch into minutes in another cell.

Any code you have that would sort my problem and increase efficiency would be much appreciated.

[mdmackillop]

Hi Baron
When you post code, use the green VBA button to format it as shown.
Regards
MD

[Bob Phillips]

Here is an example.

It hsa two independent counters, they can be started, stopped at will, independently. You can configure the intervals to be different if you wish, the setting is in the code.

[Baron808]

That's great xld!

However, I am having trouble copying the code into my spreadsheet.

When I try to run the macro it comes up with an error message which highlights this line:

Worksheets(1).Range("TimerCell1").Value = nCounter1

Could you possibly explain what I need to do to get it to work in my spreadsheet?

Thanks.

[CreganTur]

Could you possibly explain what I need to do to get it to work in my spreadsheet?

[VBA]Worksheets(1).Range("TimerCell1").Value = nCounter1[/VBA]

Have you setup a named range named TimerCell1 in your workbook?

[Baron808]

Right, I have done that...but now I have this message instead in ThisWorkbook (Code):

Worksheets(1).Range("TimerCell1").Value = ""

Thanks.

[Bob Phillips]

Post your workbook.