Stop the clock

J

J.W. Aldridge

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
 
C

Chip Pearson

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't stop a clock 1
Stop timer. 7
How do i stop a clock in excel 0
Countdown macro - display settings 2
Countdown Timer Followup 0
stop a macro 1
Can't Stop the clock 2
2 codes in one sheet 5

Top