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

manav1103

New Member
Joined
Mar 1, 2016
Messages
24
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
An example for 10min of inactivity (here I call inactivity the fact that no cell has been selected for a while).

In a module :
Code:
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 :
Code:
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).
 
Upvote 0
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:
Code:
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:
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
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

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