How to mitigate Macro's latching on to other open workbooks

[Kemidan2014]

In "ThisWorkbook"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Call StopTimer
End Sub

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

Call StopTimer
Call SetTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Call StopTimer
Call SetTimer

End Sub

In a seperate module

Dim DownTime As Date

Sub SetTimer()
    DownTime = Now + TimeValue("00:15:00")
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=False
 End Sub


Sub ShutDown()
    Application.DisplayAlerts = False
    With Workbooks("Customer Complaint Tracker.xlsm")
        .Saved = True
        .Close
    End With
End Sub

I have this autoclose macro set up in a work book, and it tends to and i dont think this is the proper term but Latched open to where it will re-open the file after it closes it when user has another unrelated excel work book open

is there anything that can be done or an option i can tell them to flag to prevent this?

 

[Kemidan2014]

Thinking about this more and reading some google articles. i am wondering since i am using Sheet Selection event in This workbook could that be some out spilling to other workbooks?
could the act of closing the workbook i want to close then excel bringing another open workbook as active be resetting the timer?
ive noticed sometimes if i access VBA control panel AFTER i close the workbook i can still see that work books macros. is ther a line of code that can purge that until the file is opened again?

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Excel Application.ontime macro re-opens workbook

I have a workbook that is using some macros to automatically close it after some idle time elapses. There is 1 issue, if the user has other excel workbooks (regardless if its xlsx or xlsm) after the

stackoverflow.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Kemidan2014]

Thank you for the advisory Rory, I'll be more careful next time

 

[Kemidan2014]

A solution was found in the Cross Post

I am posting a link back to the Cross post after the Solution was determined and tested.

Essentially There were 2 key subs, Starting a Timer and Stopping a timer. each had to have call to each other's subs at the beginning of their code. additionally added a Stop timer to the END of the closing macro. this stopped a chain of Ontime events continuing in the excel application.

Ill let the post explain the thought logic, here is the changes to the code in the seperate module.

Dim DownTime As Date

Sub SetTimer()
    Call StopTimer
    DownTime = Now + TimeValue("00:15:00")
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=True
End Sub

Sub StopTimer()
    Call SetTimer
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShutDown", Schedule:=False
 End Sub


Sub ShutDown()
    Application.DisplayAlerts = False
    With Workbooks("Customer Complaint Tracker.xlsm")
        .Saved = True
        .Close
    End With
    Call StopTimer
End Sub