• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel stop watch

R

rjwalters

New Member
Ok first let me start off I DID NOT write this code. I like the way the code works but I need it to be tweaked a little bit.


In column A I have a time (6:30)

In column B the stop watch will run

In column C is another clock and I am not sure what it is


This is how this works now, I click on the date in column A and the stopwatch starts.


I want to change the code so that it will give me a total of A & B in Column C.

I have tried several formulas and changes but to no avail.


Thoughts?


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.0") & " second "

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
 
Hi, rjwalters!


Your indented, legible and used with Option Explicit statement is this:

-----

[pre]
Code: 
Option Explicit

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 As Single, finishTime As Single, totalTime As Single, timeRow As Long, MyTime As Single
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.0") & " second "
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[/pre]
-----

Whenever you post code, you should embed it within backticks (`) to preserve indentation and spacing, as it's displayed just below the text box in this page used for posting comments.


Despite of this, it's a little confuse just from the beginning, look at the second If:

-----

If Target.Value = myVal And Target.Value <> "" Then

-----

so the question is, which value has myVal variable?


It'd be useful if you upload a sample file with the whole code include or otherwise quote the source so as to check the details. Thank you.


Regards!
 
Ok, thanks . How do I upload the file. I will just upload what I am working on since its not a classified document.
 
Hi, rjwalters!

Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

PS: if it were classified you'll find there the instructions on how to replace sensitive data with dummy data.

PS2: if it's classified and regarding stock exchange market predictions for near future, you should email me it to my personal account without changing any comma :)
 
Hi Walters ,


If you don't mind the total time being displayed in column D , try copying + pasting this code :

[pre]
Code: 
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

finishTime = Timer
totalTime = finishTime - startTime
Target.Offset(, 1).Value = Format(mytime + totalTime, "0.0") & " second "
If resetMe = True Then
Target.Offset(, 1).Value = 0
Target.Offset(, 2).Value = 0
Target.Offset(, 3).Value = 0
stopMe = True
End If

If Not stopMe = True Then
Target.Offset(, 2).Value = totalTime
Target.Offset(, 3).Value = Format(Target.Cells(1, 1) + ((mytime + totalTime) / (24 * 60 * 60#)), "hh:mm:ss AMPM")
GoTo startMe
End If

End
Else
'Not Changed
stopMe = True
Cancel = True
End If
End If
End Sub
[/pre]
The only addition is the following line of code :


Target.Offset(, 3).Value = Format(Target.Cells(1, 1) + ((mytime + totalTime) / (24 * 60 * 60#)), "hh:mm:ss AMPM")


Narayan
 
Ok I got this to work great. One last question. Is there a way to have it pause on a single click on column 1.


Right now it stops on double click anywhere other than column 1, I would like it to pause though.
 
Hi Walters ,


Unfortunately there is no event procedure for a single click of the mouse. What we have is a selection change event procedure which is triggered whenever the cursor is positioned on a new cell.


In the existing macro , the selection change event is used to initialize the value of the myVal variable.


There are 3 event procedures that take care of mouse movement and clicking ; we can try to use either the MouseUp or MouseDown procedures to do what you want.


Narayan
 
You must log in or register to reply here.
Back
Top