How do I set up timer for sheet?

[ruhan]

How do I set up timer for sheet?

 

[OssieMac]

The following is a simple example of a timer with the Start Timer and Stop
Timer subs.

Custom format the cell to display the time so that it includes the date and
the time including seconds. Example for regional date displayed in D/M/Y
format.
d mmm yyyy h:mm:ss

'RunWhen must be declared at top of
'a standard Module before any subs

Public RunWhen As Date

Sub StartTimer()
Dim TimerIntervals
TimerIntervals = 1 'In seconds

RunWhen = Now + _
TimeSerial(0, 0, TimerIntervals)

ActiveSheet.Range("A1") = Now

Application.OnTime _
EarliestTime:=RunWhen, _
Procedure:="StartTimer", _
Schedule:=True
End Sub

Sub StopTimer()
'On Error required in case timer already stopped
'RunWhen must be same value that started timer

On Error Resume Next

Application.OnTime _
EarliestTime:=RunWhen, _
Procedure:="StartTimer", _
Schedule:=False

End Sub

 

[OssieMac]

The following comment in my example Sub StopTimer might be misleading.
'RunWhen must be same value that started timer.

Probably better to say
'RunWhen must be same value as the last value USED in StartTimer.

 

[L. Howard Kittle]

Here is a plain jane version with a couple bells and whistles.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
i = Range("F1").Value + 1
Range("F1").Value = Range("F1").Value + 1

Application.EnableEvents = False

CreateObject("WScript.Shell").Popup "Some stupid message" _
& vbCr & "timed for 5 seconds!" _
& vbCr & "And you have run this macro " _
& vbCr & i & " times", _
5, "Change this name to suit you!"

Application.EnableEvents = True
End Sub

HTH
Regards,
Howard