how do I create a running clock?

Question

I have an excel spreadsheet that I have inserted form buttons into.  I have a button in cells B2-B144 that, when pushed, will display the current time.  In Cells C2-C144 I have the same button.  I am trying to get a running clock in cell D2 that will start once the button in B2 is pushed and will stop once the button in C2 is pushed.  I currently have =IF(B2="","",IF(C2="",NOW()-B2,C2-B2)) in cell D2 and copied it down through D144 but it won't run continuously...Any suggestions?

Answer 1

In D2 enter:

=NOW()

Enter the following macros in a standard module:

Public RunWhen As Double

Public Const cRunIntervalSeconds = 1

Public Const cRunWhat = "The_Sub"

Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)

Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _

     schedule:=True

End Sub

Sub The_Sub()

'

Calculate

'

StartTimer

End Sub

Sub StopTimer()

   On Error Resume Next

   Application.OnTime earliesttime:=RunWhen, _

       procedure:=cRunWhat, schedule:=False

End Sub

Assign the Start macro to the B2 button and the Stop macro to the C2 button.

Answer 2

When I click the button in B2 I want it to start a stopwatch (so to speak) in D2 and I need to be able to start a different stopwatch in,say, B55 and it be controlled separately.

Answer 3

I tried that also but I kept getting the "ambiguous name" error.  I need it to run individually in different cells...sorry if I am not making any sense....it would be helpful if I could show you my spreadsheet...is there a way to do that? Any help given is appreciated.