To cancel a pending OnTime event, you must provide the *exact* time
that the event is scheduled to run. Thus, you should store that value
in a module-scoped variable and use that value to schedule and cancel
the OnTime event. E.g.,

Dim RunWhen As Double

Sub Clock()
' your code
RunWhen = Now + TimeSerial(0,0,1)
Application.OnTime RunWhen, "Clock", , True
End Sub

Sub StopTheClock()
Application.OnTime RunWhen, "Clock", , False
End Sub

See www.cpearson.com/Excel/OnTime.aspx for more information about
working with OnTime.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Tue, 10 Feb 2009 13:22:52 -0800 (PST), "J.W. Aldridge"
wrote:

The following formula works fine to give me the clock i need however:
The clock seems to prevent me from closing the workbook.
I have it set to run on open to ensure that it is running properly.
(2nd sub)
How do i stop from running this on workbook close?


Sub clock()
If ThisWorkbook.Worksheets(1).Range("e22").Value = "X" Then Exit Sub
ThisWorkbook.Worksheets(1).Range("e21").Value = Format(Now, "hh:mm:ss
AM/PM")
Application.OnTime Now + TimeSerial(0, 0, 1), "clock"
End Sub


Private Sub Workbook_Open()
Application.Run "clock"
End Sub