Apparently putting a running timer on a worksheet that will be edited isn't particularly simple. I found the following by searching the MrExcel forum for the word "timer". The linked thread discusses the problem and a solution, but it displays the current time rather than elapsed time, and the time disappears when the stop button is pressed.
http://www.mrexcel.com/forum/showpost.php?p=1949222&postcount=3
I have modified the code just a bit for your needs, but it has certain limitations. As written here, it displays elapsed time in cell A1, and uses a cell assumed to be out of range of your existing workbook, cell AB1, to hold the time that the start button was pressed, so that Cell A1 shows elapsed time rather than current time.
When the Stop button is pressed, it freezes the elapsed time in the cell. If you need to use different cells for this, you will need to adjust the parts shown in
Red. I have tested this a bit, but certainly not within your application. Since this isn't my code, I won't be able to help much if it doesn't work.
Code:
Global myStartTime As Double
Private Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerfunc As Long) As Long
Private Declare Function KillTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long
Private lTimerId As Long
Private oTargetCell As Range
Private Sub TimerProc()
On Error Resume Next
oTargetCell.Calculate
End Sub
Sub AddClock(Cell As Range)
Set oTargetCell = Cell
Cell.FormulaR1C1 = "=TEXT(NOW() [COLOR=red]-RC[27][/COLOR],""hh:mm:ss"")"
lTimerId = SetTimer(0, 0, 100, AddressOf TimerProc)
End Sub
Sub RemoveClock(Cell As Range)
KillTimer 0, lTimerId
Cell.FormulaR1C1 = Cell.Value
End Sub
Sub CreateClock()
Range("[COLOR=red]AB1[/COLOR]").Value = Now() 'this should be a cell that's outside your used data range
AddClock Range("[COLOR=red]a1[/COLOR]")
End Sub
Sub DestroyClock()
RemoveClock Range("[COLOR=red]a1[/COLOR]")
End Sub
To use this, put the code into a standard module, then add 2 buttons to your worksheet (From the developer tab, choose Insert, then choose the button element to draw the button.) Link the Start button to the macro CreateClock, and the Stop button to the macro DestroyClock.
Hope this helps,
Cindy
