Try the attached. This isn't something I do often, but I just helped someone with it the other day. This seems to work, don't know if it's a best-practice way to get it done.
In the List WORKSHEET code module put this.
Option Explicit
Private Sub WORKSHEET_ACTIVATE()
Me.Range("E1").Value = startVal
Application.Run "onActivate"
End Sub
Private Sub WORKSHEET_DEACTIVATE()
On Error Resume Next
Application.Run "onDeactivate"
End Sub
In a separate (non-object) code module put this:
(Also I gave the "List" worksheet a VBA project 'codeName' of W_LIST)
Option Explicit
Public RunWhen As Double
Public Const startVal = 1
Public Const inc = 1
Public Const howOften = 5
Public Const runWhat = "Active_Process"
Sub scheduleMe(ByVal mySub As String)
RunWhen = Now + TimeSerial(0, 0, howOften)
Application.OnTime EarliestTime:=RunWhen, Procedure:=runWhat, schedule:=True
End Sub
Sub Active_Process()
W_LIST.Range("E1").Value = W_LIST.Range("E1").Value + inc
Call scheduleMe(runWhat)
End Sub
Private Sub onActivate()
W_LIST.Range("E1").Value = startVal
Call scheduleMe(runWhat)
End Sub
Private Sub onDeactivate()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=runWhat, schedule:=False
End Sub
Bookmarks