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

Himateja

New Member
Joined
Aug 15, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,407
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
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:
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    CancelSave1
End Sub

Private Sub Workbook_Open()
    ScheduleSave1
End Sub


this goes in the appropriate worksheet module:
VBA Code:
Private Sub CommandButton2_Click()
    MsgBox "Please Make Sure Your copy is Saved"
    CancelSave1
    MsgBox "Successful"
End Sub
 

Himateja

New Member
Joined
Aug 15, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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:
VBA Code:
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:
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    CancelSave1
End Sub

Private Sub Workbook_Open()
    ScheduleSave1
End Sub


this goes in the appropriate worksheet module:
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,407
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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.
VBA Code:
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
 

Forum statistics

Threads
1,147,481
Messages
5,741,400
Members
423,657
Latest member
Medrok2021

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