Macro to auto save and close excel file after some time of inactivity

[manav1103]

Hello Everyone,

I want to have a help on simple macro which auto save and closes excel file after certain time of inactivity.

Kindly help.

 

[NotoriousPopol]

Should it be for one specific file or for many?

 

[manav1103]

For one specific file only...

 

[NotoriousPopol]

An example for 10min of inactivity (here I call inactivity the fact that no cell has been selected for a while).

In a module :

Dim TheTime As Long

Sub StartTimer()

TheTime = Timer
Application.OnTime Now + TimeValue("00:10:00"), "CloseSave"


End Sub


Sub CloseSave()

If Timer - TheTime > 580 Then
    ThisWorkbook.Close SaveChanges:=True
End If

End Sub

In the Workbook :

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

StartTimer

End Sub

The idea : everytime the cell selection changes, we start a clock that will try to save and close the workbook. If, during this time, we changed the selection, this won't happen. The "<580" should correspond to the number of seconds you want (here 600, I put 580to be sure).

 

[Peter h]

Here is the code I use to accomplish this. I have mine set to save and close after 40 minutes of inactivity, but that can be changed to whatever time you want.

In Module1:

Dim CloseTime As Date
Sub TimeSetting()
    CloseTime = Now + TimeValue("00:40: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

In ThisWorkbook:

Private Sub Workbook_Open()

Call TimeSetting
 
End Sub



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Call TimeStop
   Call TimeSetting
End Sub

 

[Steve_f]

Hello Peter, how are you?

I have used your code (set it to 1 minute just fro testing purposes) after some sorting code in a spreadsheet but the sorting function is throwing up errors. Do I need to have two separate pieces of code one to sort and the other to auto save and close? Sorry very new to coding and in the middle or reading a few books I have now purchased. I have copied the code below:

SHEET CODE
Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Columns(12)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub


Sheet2.Unprotect "trial1"


Application.ScreenUpdating = False


If Target.Value = "Yes" Then
Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
End If


Sheet2.Protect "trial1"


Application.ScreenUpdating = True


End Sub


Dim CloseTime As Date
Sub TimeSetting()
CloseTime = Now + TimeValue("00:01: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

THISWORKBOOK CODE
Private Sub Workbook_Open()


Call TimeSetting

End Sub






Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call TimeStop
Call TimeSetting
End Sub


Any help you can give is greatly appreciated?
Steve