Issue with Macro called macro to close WB.

[selkov]

I have a WB that opens and if left open for 2 minutes runs the macro below to close it. But if I close the workbook before two minutes it still runs the macro and for some reason reopens it and then closes it.

So I tried to stop the second macro from running [WbCls] by checking to be sure the first is open first, but that does not work either.


Can any one help?
Thanks


Sub TMR()
Sheets("EXCWOS").Select
Application.OnTime Now + TimeValue("00:1:30"), "SaveWb"
End Sub

Sub WbCls()
For Each ws In Sheets
If ws.Name = "EXCWOS" Then
Application.DisplayAlerts = False
Workbooks("ExcludedWo").Close (True)
Application.DisplayAlerts = True
Application.Quit
End If
End Sub

 

[John_w]

Store the close time in a variable with module scope and cancel the OnTime before closing; something like this:

Private CloseTime As Date

Sub TMR()
Sheets("EXCWOS").Select
CloseTime = Now + TimeValue("00:01:30")
Application.OnTime CloseTime, "SaveWb"
End Sub

Sub WbCls()
For Each ws In Sheets
If ws.Name = "EXCWOS" Then
Application.DisplayAlerts = False
    On Error Resume Next
    Application.OnTime CloseTime, "SaveWb", , False
    On Error Goto 0
Workbooks("ExcludedWo").Close (True)
Application.DisplayAlerts = True
Application.Quit
End If
End Sub

 

[DanteAmor]

In the events of thisworkbook:

Private Sub Workbook_open()
    Call TMR
End Sub
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime ahora + TimeValue("00:01:30"), "SaveWb", , Schedule:=False
End Sub

in a module:

Public ahora
'
Sub TMR()
    Sheets("EXCWOS").Select
    ahora = Now
    Application.OnTime ahora + TimeValue("00:01:30"), "SaveWb"
End Sub
'
Sub SaveWb()
     'here your code
    ThisWorkbook.Save
    ThisWorkbook.Close
End Sub

 

[selkov]

Thanks guys. tried both macros and suffer still from the same issue.

 

[selkov]

Found the solution:

Sub SaveWb()
For Each WS In Sheets
If WS.Name = "EXCWOS" Then GoTo prs
GoTo xit
prs:
Application.DisplayAlerts = False
Workbooks("ExcludedWo").Close (True)
Application.DisplayAlerts = True
xit:
Next WS
End Sub

 

[selkov]

Well now there is another issue. If there is an open excel wb and I open this one and I run the macro it works but leaves an open temporary. The temp file will not close until I close all open excel instances. That is not convenient. Any Idea how to work around that?


Sub SaveWb()
For Each WS In Sheets
If WS.Name = "EXCWOS" Then GoTo prs
GoTo xit
prs:
Application.DisplayAlerts = False
Workbooks("ExcludedWo").Close (True)
Application.DisplayAlerts = True
xit:
Next WS
End Sub