Stop running OnTime macro when closed. Please Help!

brolsen

New Member
Joined
Aug 11, 2011
Messages
14
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!!!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

brolsen

New Member
Joined
Aug 11, 2011
Messages
14
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

Board Regular
Joined
Nov 3, 2014
Messages
71
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

Board Regular
Joined
Nov 3, 2014
Messages
71
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

Board Regular
Joined
Nov 3, 2014
Messages
71
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
 

Forum statistics

Threads
1,136,909
Messages
5,678,522
Members
419,769
Latest member
Sparks66

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top