stop macro running when workbook has closed

Hi,

I am struggling to prevent the application.ontime to stop running when I am either not on that workbook or have already closed that workbook.
This is my code:

Public RunWhen As Double
Public Const RunWhat = "RunEveryTwoMinutes"


Sub RunEveryTwoMinutes()
Call gp_daybook
Call missing
RunWhen = Now + TimeValue("00:00:15")
Application.OnTime earliesttime:=RunWhen, procedure:=RunWhat, schedule:=True

End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=RunWhat, schedule:=False
End Sub

Probably pretty ropey code, but best I can do as I am not the best at it. Do I need to have code that runs when I close the workbook or something?

I know the name doesn't match with the timevalue by the way, I just put it shorter as a test

Originally Posted by nickymac

I know the name doesn't match with the timevalue by the way, I just put it shorter as a test

I have solved this now with this:

Public RunWhen As Double
Public Const RunWhat = "RunEveryTwoMinutes"


Sub RunEveryTwoMinutes()
'I have kept the name the same but changed the runtime to every 15 minutes
'Call gp_daybook
RunWhen = Now + TimeValue("00:02:00")
Application.OnTime EarliestTime:=RunWhen, Procedure:=RunWhat, Schedule:=True

End Sub

and in the workbook code:

Private Sub Workbook_Open()

Call RunEveryTwoMinutes

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, _
Procedure:=RunWhat, Schedule:=False
MsgBox "Bye Bye"
End Sub