Odd issue with Save and Close Macro

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
191
Office Version
  1. 365
Platform
  1. Windows
I had set this macro up as an automatic Save and Close if there was no activity in x minutes.

However, I have noticed lately that it is saving an closing even while I am working on it.

Especially when I am make important changes to the workbook.

Any Idea?



Dim CloseTime As Date
Sub TimeSetting()
CloseTime = Now + TimeValue("00:15:00")
On Error Resume Next
Application.OnTime EarliestTime:=CloseTime, _
Procedure:="SavedAndClose", Schedule:=True
End Sub
Sub TimeStop()
On Error Resume Next
Application.OnTime EarliestTime:=CloseTime, _
Procedure:="SavedAndClose", Schedule:=False
End Sub
Sub SavedAndClose()
ActiveWorkbook.Close Savechanges:=True
End Sub
 
VBA Code:
Dim CloseTime As Date

' Subroutine to reset the timer
Sub TimeSetting()
    CloseTime = Now + TimeValue("00:15:00")  ' Set time for 15 minutes from now
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
    Procedure:="SavedAndClose", Schedule:=True
End Sub

' Subroutine to stop the scheduled close
Sub TimeStop()
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
    Procedure:="SavedAndClose", Schedule:=False
End Sub

' Subroutine that closes the workbook
Sub SavedAndClose()
    ActiveWorkbook.Close SaveChanges:=True
End Sub

' Automatically restart the timer whenever there is user activity
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    TimeStop  ' Stop the previous timer
    TimeSetting  ' Restart the timer
End Sub

Private Sub Workbook_Open()
    TimeSetting  ' Initialize the timer when the workbook is opened
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TimeStop  ' Stop the timer when closing the workbook
End Sub
 
Upvote 0
VBA Code:
Dim CloseTime As Date

' Subroutine to reset the timer
Sub TimeSetting()
    CloseTime = Now + TimeValue("00:15:00")  ' Set time for 15 minutes from now
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
    Procedure:="SavedAndClose", Schedule:=True
End Sub

' Subroutine to stop the scheduled close
Sub TimeStop()
    On Error Resume Next
    Application.OnTime EarliestTime:=CloseTime, _
    Procedure:="SavedAndClose", Schedule:=False
End Sub

' Subroutine that closes the workbook
Sub SavedAndClose()
    ActiveWorkbook.Close SaveChanges:=True
End Sub

' Automatically restart the timer whenever there is user activity
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    TimeStop  ' Stop the previous timer
    TimeSetting  ' Restart the timer
End Sub

Private Sub Workbook_Open()
    TimeSetting  ' Initialize the timer when the workbook is opened
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TimeStop  ' Stop the timer when closing the workbook
End Sub
Can I keep the quoted instructions in green in the macro? Will it be ignored?
 
Upvote 0
Yes, the green text represents comments that provide descriptions or information about the code. This allows other users to understand the purpose of the code when they view it.
 
Upvote 0

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
Back
Top