Odd issue with Save and Close Macro

[DPChristman]

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

 

[arunsjain]

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

 

[DPChristman]

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?

 

[arunsjain]

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.

 

[DPChristman]

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.

Thanks - I will give this a shot.