Stop running OnTime macro when closed. Please Help!

[brolsen]

Hello!

I'm really hoping someone can help me. I have searched and searched and I cannot find a solution that works for me.
Our network at work has an issue that when we share an excel file, if anyone on Windows 7 (most of us are on XP) opens the file, it will automatically unshare it.
So, we needed a file that would close automatically on it's own. I am kinda newbie but I did the following:

ThisWorkbook Code
Private Sub Workbook_Open()
Application.Run "OnTime"
End Sub

Module Code
Sub OnTime()
Application.OnTime Now + TimeValue("00:4:00"), "MyMacro"
End Sub
Sub MyMacro()
MsgBox "Finish up quickly as workbook will automatically close in 1 minute"
Application.OnTime Now + TimeValue("00:01:00"), "CloseMacro"
End Sub
Sub CloseMacro()
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
Application.Quit
End Sub

So the macro runs great when everyone has it enabled, but there is still a problem. If the user lets the macro close the window for them after the 5 minutes, the macro runs perfect. However, if the user closes the window on their own and has other excel windows open still, the code will keep running and after 5 minutes they will get the warning message, the file will re-open on their computer, and the code will keep doing this on a loop.The only way to stop this is to close down ALL excel windows.

I don't have the article but I thought I read somewhere that global time instead of exact time would fix this issue, but I have no idea how to do this. Can someone please help me out and figure out a way to make the timed macro stop if the user closes the excel before the CloseMacro runs and wants to leave their other excel windows open?

This would help so much!!!

 

[brolsen]

This is what needs to be done if anyone ever has this issue, I got help on ExcelForum.com:

ThisWorkbook Code
Private Sub Workbook_Open()
Application.Run "StartTimer"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run "StopTimer"
End Sub

Module 1 Code
Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' two minutes
Public Const cRunWhat = "CloseMacro" ' the name of the procedure to run

Module 2 Code
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub
Sub CloseMacro()
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
Application.Quit
StartTimer ' Reschedule the procedure
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub

The BeforeClose ensured that the timer is stopped before excel closes! Hooray! The search is OVER!!!

 

[BonnieM]

Hi, I am trying to solve a similar problem with my code popping up a MsgBox after idle time. Like your issue, after user closes file, it automatically reopens. Can you advise how to use the solution above to accomplish the same objective with my code below?

This Workbook Code

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue(gsIdletime), "CheckActivity"


End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
UpdateLastUsed
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
UpdateLastUsed
End Sub



Module 1 Code

Public gLastUsed As Date
Public Const gsIdletime As String = "00:10:00"


Public Sub UpdateLastUsed()
gLastUsed = Now()
End Sub
Public Sub CheckActivity()
If gLastUsed + TimeValue(gsIdletime) < Now Then
MsgBox "You've been idle since " & Format(gLastUsed, "hh:mm:ss") & vbCr & " Please Save and Close Now."
End If
Application.OnTime Now + TimeValue("00:2:00"), "CheckActivity"


End Sub

 

[BonnieM]

Looking for some feedback on this one. I've now got the message to pop up and repeat with Start and Stop timer, but it happens whether there has been activity or not. I really only want it to occur when the sheet has been idle

 

[BonnieM]

Yoo-hoo! Trying to bump this up to new posts and possibly get some help? Looking for message to pop-up when sheet is idle, and not automatically close file, nor continue to run after file is closed. This is my current code which pops up whether idle or not:

This Workbook
Private Sub Workbook_Open()
Application.Run "StartTimer"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run "StopTimer"
End Sub


Module 1
Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' 5 minutes
Public Const cRunWhat = "CloseMacro" ' the name of the procedure to run


Module 2
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub
Sub CloseMacro()
Application.DisplayAlerts = False
'ThisWorkbook.Save
'ThisWorkbook.Close
'Application.Quit
MsgBox ("This File Has Been Idle for 5 Minutes" & vbCr & "Please Save and Close Now")
StartTimer ' Reschedule the procedure
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub