Create A Stopwatch in Excel

[hrayani]

Hello To All,

How can i input a stopwatch in excel sheet with a Start, Stop, and Reset Button....

Awaiting reply,

Humayun

 

[VoG]

That might be possible but it would 'lock' Excel I think.

 

[hrayani]

can we give it a try??? If yes then HOW?

 

[hrayani]

Dear Vog,

I just noticed that the reset function is not working

As soon as i run the reset macro

following occurs and i am into the VBA editor and the yellow arrow highlights the following lines

Sub StopClock()
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
End Sub

Any idea what's wrong with it

Humayun

 

[VoG]

Try

Sub StopClock()
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
On Error GoTo 0
End Sub

 

[hrayani]

Thanks very much now its OK 100%

pls tell me what to do if i want the seconds to show in miliseconds like its showing in 2 digits. how can we increase it.

Humayun

 

[hrayani]

i am sorry VOG for giving you trouble...

there is one more issue i just noticed and that is when i run the STARTCLOCK macro the clock and when i run the STOPCLOCK the clock stops

But, as soon as i run the STARTCLOCK again without running the reset , the clock starts from zero again... whereas, it should continue from where last it was stopped..

sorry once again for giving you trouble.

Humayun

 

[VoG]

Try

Dim NextTick As Date, t As Date

Sub StartClock()
If Range("IV1").Value = True Or Range("IV1").Value = "" Then
    t = Time
    Range("IV1").Value = False
End If
Call TickTock
End Sub

Private Sub TickTock()
Range("A1").Value = Format(Time - t, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub

Sub StopClock()
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
On Error GoTo 0
End Sub

Sub Reset()
Call StopClock
Range("A1").Value = 0
Range("IV1").Value = True
End Sub

 

[hrayani]

no Dear its not working...

I reset the clock
then
i run the STARTCLOCK
when it reached 15 seconds i STOPED the CLOCK

then
i press the STARTCLOCK after 7 seconds
the clock resumes from 22 seconds,, whereas, it should resume from 15 seconds.

what i think is its even running after the STOPCLOCK but not showing on the screen

Any Idea?

Humayun

 

[VoG]

Try

Dim NextTick As Date, t As Date

Sub StartClock()
If Range("IV1").Value = True Or Range("IV1").Value = "" Then
    t = Time
Else
    t = Time - Range("IV2").Value
End If
Call TickTock
End Sub

Private Sub TickTock()
Range("A1").Value = Format(Time - t, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub

Sub StopClock()
Range("IV1").Value = False
Range("IV2").Value = Range("A1").Value
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
On Error GoTo 0
End Sub

Sub Reset()
Call StopClock
Range("A1").Value = 0
Range("IV1").Value = True
Range("IV2").ClearContents
End Sub

 

[hrayani]

Thanks VOG... Now its Ok

One last question is about the mili or nano seconds.... pls let me know how to do it.