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!!!
 

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,082,336
Messages
5,364,701
Members
400,811
Latest member
MSBINinja

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top