PDA

View Full Version : Stopwatch



Baron808
09-02-2008, 09:18 AM
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
09-02-2008, 09:21 AM
Post the code, we are not mind readers.

Baron808
09-02-2008, 09:33 AM
Sorry! This is the code:


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

Thanks!

TomSchreiner
09-02-2008, 09:49 AM
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
09-02-2008, 09:54 AM
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
09-02-2008, 10:01 AM
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
09-02-2008, 10:50 AM
Hi Baron
When you post code, use the green VBA button to format it as shown.
Regards
MD

Bob Phillips
09-02-2008, 02:52 PM
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
09-03-2008, 07:50 AM
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
09-03-2008, 07:54 AM
Could you possibly explain what I need to do to get it to work in my spreadsheet?

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

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

Baron808
09-03-2008, 08:14 AM
Right, I have done that...but now I have this message instead in ThisWorkbook (Code):

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

Thanks.

Bob Phillips
09-03-2008, 08:18 AM
Post your workbook.