"joeu2004" wrote in message
...
"Victor Delta" wrote:
"joeu2004" wrote:
Sub stopIt()
Range("A1").ClearContents
On Error Resume Next
Application.OnTime endTime, "stopIt",, False
End Sub
[....]
Is there any way to force 'stopIt' if the spreadsheet
is closed during the 1 hour?

You could create the following Workbook_Close event macro (untested;
beware of syntax errors):

Sub Workbook_Close()
stopIt
End Sub

The Workbook_Close event macro goes in the Workbook object, not in a
worksheet or normal module. To ensure proper syntax of the declaration,
click on the pull-down menus at the top of the VBA editing pane.

That paradigm presumes that stopIt is in a normal module (not a worksheet
object), and it is not Private.

Of course, there is no need to stop the timer. That should be done
automagically by Excel.

But I presume you want to clear A1 (in my example).

Thanks. Following your guidance on the pull-down menus, I've ended up with:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Run "stopIt"

End Sub

which seems to work well.

Previously I found that if you closed and then reopened the spreadsheet, the
timer was still running which was not what I wanted in that situation. (I've
also set up buttons to start and stop the timer.)

Thanks again,

V