Workbook reopens automatically

[Guest]

I have a workbook that uses a clock procedure to update the sheet every minute. I have created a procedure called StopClock that is called from Workbook_BeforeClose. However, after I close the workbook and leave Excel open the workbook application relaunches itself. The StopClock procedure is listed below. Is there anything that I am missing

Public Sub StopClock(
'Cancels the Clock procedur
On Error Resume Nex
Application.OnTime ClockTick, "SQLClock", , Fals
End Su

'Here is the clock procedur

Public Sub Clock(
Dim ClockTick As Dat
Sheet1.Range("J8") = Tim

'Update the dashboard every minut
ClockTick = Now + TimeValue("00:01:00"

'Run these SQL queries for the update
With Applicatio
.OnTime ClockTick, "Clock
.OnTime ClockTick, "AgentRequests
.OnTime ClockTick, "AgentEscalate
.OnTime ClockTick, "AgentClosed
.OnTime ClockTick, "TopProblem
.OnTime ClockTick, "ReqOpen
.OnTime ClockTick, "ReqResolve

End Wit
End Sub

 

[Frank Kabel]

Hi
not tested but I think you have to stop the OnTime method for all your
procedures

 

[Guest]

Frank thank you but..

I added all the procedures referenced by the OnTime method to the StopClock procedure. Although this made sense the workbook still relaunches itself if Excel is left open. I've checked everywhere else in the code and I can't see where there would be a procedure or an event causing this problem. This is really frustrating. I've searched Google and Excel help but I'm not getting any clues... Does anyone else have something I can try?

 

[Steve Garman]

I guess you're not using Option Explicit or this would show up.

ClockTick is dimmed in sub Clock and is therefore local to that sub

The ClockTick you are using in sub StopClock is a different variable.

Try moving "Dim ClockTick As Date" to the top of your module, outside
all the subs.

 

[Guest]

Frank and Steve

Thanks very much for your input. Both of your suggestions finally resolved the problem and the worksheet stays closed.