OnTime Applying all open Workbook and reopening the Excel sheet which was closed

[Himateja]

I have This in My ThisWorkbook

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook.Worksheets("My_Subs")
Application.Run "CommandButton2_Click"
End With
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:30"), "Save1"
End Sub

And this in Module1

Sub Save1()
Dim path1 As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("My_Subs").Select
Cells.Select
Selection.ColumnWidth = 12
path1 = "\\ant.amazon.com\dept-as\hyd\hyd1\HR\NA-ERC\ERP Workallocation\Work Extraction- Asc Files\Associate Data\" & Environ("username") & ".xlsx"

If Len(Dir(path1, vbDirectory)) = 0 Then
Set wkb = Workbooks.Add
wkb.SaveAs path1
End If


Set destwb = Workbooks.Open(path1)
Set dest_sheet = destwb.Worksheets("Sheet1")
Set currSheet = ThisWorkbook.Sheets("My_Subs")

dest_sheet.Cells.Delete
currSheet.Cells.Copy _
Destination:=dest_sheet.Cells
destwb.Save
destwb.Close
Application.OnTime earliesttime:=Now + TimeValue("00:00:30"), Procedure:="Save1"
ThisWorkbook.Save
End Sub

Private Sub CommandButton2_Click()
MsgBox "Please Make Sure Your copy is Saved"
Application.OnTime earliesttime:=Now + TimeValue("00:10:00"), Procedure:="Save1"
Schedule = False
MsgBox "Successful"
End Sub

The problem i'm facing is i have to use few different sheets and one workbook has be saved for every 10 minutes. so i've used OnTime to save. So thisworkbook calls Save1 once the workbook is open. Save1 recalls itself to run for every 10 minutes.

I've added this Beforeclose code to stop ontime macro before close. But this applies to all workbooks open and doesn't run when any other workbook is open. this triggeres the ontime code and reopens my sheet which has already been closed.

 

[GWteB]

The problem you are facing is caused by the fact that the OnTime is not canceled anywhere.
The CommandButton2_Click procedure was supposed to do that but in fact it schedules for a second time, causing the Save1 procedure to be called twice (or more times) every 10 minutes.
The Schedule = False statement should have been on the same line of code as a parameter of the OnTime, but it is on a separate line and therefore it just creates a local boolean variable named Schedule and valued False. Just restoring the foregoing is not enough. To cancel an OnTime schedule, the exact time must also be specified. To this end, it must be stored somewhere beforehand.

Regarding all this your code could look something like below.

this goes in a standard module:

Public ScheduledTime    As Date        ' <<< storage for scheduled date & time

Sub Save1()
    Dim path1 As String
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheets("My_Subs").Select
    Cells.Select
    Selection.ColumnWidth = 12
    path1 = "\\ant.amazon.com\dept-as\hyd\hyd1\HR\NA-ERC\ERP Workallocation\Work Extraction- Asc Files\Associate Data\" & Environ("username") & ".xlsx"

    If Len(Dir(path1, vbDirectory)) = 0 Then
        Set wkb = Workbooks.Add
        wkb.SaveAs path1
    End If

    Set destwb = Workbooks.Open(path1)
    Set dest_sheet = destwb.Worksheets("Sheet1")
    Set currSheet = ThisWorkbook.Sheets("My_Subs")

    dest_sheet.Cells.Delete
    currSheet.Cells.Copy _
        Destination:=dest_sheet.Cells
    destwb.Save
    destwb.Close
    ScheduleSave1                        ' <<< reschedule
    ThisWorkbook.Save
End Sub

Public Sub ScheduleSave1()
    ScheduledTime = Now + TimeValue("00:10:00")
    Application.OnTime earliesttime:=ScheduledTime, Procedure:="Save1"
End Sub

Public Sub CancelSave1()
    Application.OnTime earliesttime:=ScheduledTime, Procedure:="Save1", Schedule:=False
End Sub

this goes in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    CancelSave1
End Sub

Private Sub Workbook_Open()
    ScheduleSave1
End Sub

this goes in the appropriate worksheet module:

Private Sub CommandButton2_Click()
    MsgBox "Please Make Sure Your copy is Saved"
    CancelSave1
    MsgBox "Successful"
End Sub

 

[Himateja]

The problem you are facing is caused by the fact that the OnTime is not canceled anywhere.
The CommandButton2_Click procedure was supposed to do that but in fact it schedules for a second time, causing the Save1 procedure to be called twice (or more times) every 10 minutes.
The Schedule = False statement should have been on the same line of code as a parameter of the OnTime, but it is on a separate line and therefore it just creates a local boolean variable named Schedule and valued False. Just restoring the foregoing is not enough. To cancel an OnTime schedule, the exact time must also be specified. To this end, it must be stored somewhere beforehand.

Regarding all this your code could look something like below.

this goes in a standard module:

Public ScheduledTime    As Date        ' <<< storage for scheduled date & time

Sub Save1()
    Dim path1 As String
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheets("My_Subs").Select
    Cells.Select
    Selection.ColumnWidth = 12
    path1 = "\\ant.amazon.com\dept-as\hyd\hyd1\HR\NA-ERC\ERP Workallocation\Work Extraction- Asc Files\Associate Data\" & Environ("username") & ".xlsx"

    If Len(Dir(path1, vbDirectory)) = 0 Then
        Set wkb = Workbooks.Add
        wkb.SaveAs path1
    End If

    Set destwb = Workbooks.Open(path1)
    Set dest_sheet = destwb.Worksheets("Sheet1")
    Set currSheet = ThisWorkbook.Sheets("My_Subs")

    dest_sheet.Cells.Delete
    currSheet.Cells.Copy _
        Destination:=dest_sheet.Cells
    destwb.Save
    destwb.Close
    ScheduleSave1                        ' <<< reschedule
    ThisWorkbook.Save
End Sub

Public Sub ScheduleSave1()
    ScheduledTime = Now + TimeValue("00:10:00")
    Application.OnTime earliesttime:=ScheduledTime, Procedure:="Save1"
End Sub

Public Sub CancelSave1()
    Application.OnTime earliesttime:=ScheduledTime, Procedure:="Save1", Schedule:=False
End Sub

this goes in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    CancelSave1
End Sub

Private Sub Workbook_Open()
    ScheduleSave1
End Sub

this goes in the appropriate worksheet module:

Private Sub CommandButton2_Click()
    MsgBox "Please Make Sure Your copy is Saved"
    CancelSave1
    MsgBox "Successful"
End Sub

But the before close event still doesn't seem to work

 

[GWteB]

There are a number of possible causes for this. For example, it could be that events are not enabled within Excel. This is unlikely, because then the WorkBook_Open event handler would not have been fired either and no macro would haven been scheduled. Should this nevertheless be the case, a macro like this can solve this problem.

Public Sub EnableEvts()
    Application.EnableEvents = True
End Sub

The most obvious however, is that the value of the ScheduledTime variable is lost any time you work on your code within the VBE.
Therefore, it is recommended to use a worksheet range for storage. If I were you I would manually create a new worksheet, hide this sheet and force your macros to save/obtain the scheduled time in/from cell A1. Code for this might look like below. After you've made these changes, save your workbook and close Excel completely (!!). Then restart Excel, open your macro enabled workbook containing this code and see whether this has resolved your issue.

Public Sub ScheduleSave1()
    With ThisWorkbook.Worksheets("YourHiddenSheet").Range("A1")
        .Value = Now + TimeValue("00:10:00")
        Application.OnTime EarliestTime:=.Value, Procedure:="Save1"
    End With
End Sub

Public Sub CancelSave1()
    With ThisWorkbook.Worksheets("YourHiddenSheet").Range("A1")
        Application.OnTime EarliestTime:=.Value2, Procedure:="Save1", Schedule:=False
    End With
End Sub