Is it possible to autosave and close an Excel workbook after a period of inactivity?
Thanks
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Excel autosave
0
Answers
0
Welcome to the forum E199,
I haven't used this for quite a while, but it has worked for me in the past.
In a regular module put:
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 SubThen in the Workbook code put:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
StartTimer
End SubHope this helps.
Discussion
Hi WillieD24,
Thanks heaps for that.
It works perfectly.
El
Thanks heaps for that.
It works perfectly.
El
El99 Mar 17, '22 at 3:30 am
El99- please don't forget to mark Willie's answer as Selected (according to the Rules of the Forum)
John_Ru (rep: 6142) Mar 17, '22 at 7:29 am
@El99 - I notice that Willie's macro works well but I think it won't in the (perhaps unlikely) event that a user opens the workbook and does nothing- the timer won't be started so it won't save an close. To prevent that and to warn users anyway, you might like to add this Workbook macro (so opening the file sets the timer going):
@Willie - seems to me that every single selection change schedules an OnTime action but your <580 (seconds) check prevents a premature workbook close if a selection change was made meantime. Did you consider storing its EarliestTime argument - Now + TimeValue("00:10:00") - on scheduling then using that to cancel the action when the next selection change is detected?
Private Sub Workbook_Open()
StartTimer
MsgBox "Remember that this workbook will close automatically after 10 minutes of inactivity"
End Sub@Willie - seems to me that every single selection change schedules an OnTime action but your <580 (seconds) check prevents a premature workbook close if a selection change was made meantime. Did you consider storing its EarliestTime argument - Now + TimeValue("00:10:00") - on scheduling then using that to cancel the action when the next selection change is detected?
John_Ru (rep: 6142) Mar 18, '22 at 4:14 am
Hi John,
This is some code I cobbled together many years ago using code I found on-line to solve a problem for a friend. I have not used it for any of my personal projects. My friend was happy with how things worked so I never gave it any more thought. Your knowledge of matters like this excedes mine, so am confident that your suggestion would have been a better approach. I appreciate the insight.
This is some code I cobbled together many years ago using code I found on-line to solve a problem for a friend. I have not used it for any of my personal projects. My friend was happy with how things worked so I never gave it any more thought. Your knowledge of matters like this excedes mine, so am confident that your suggestion would have been a better approach. I appreciate the insight.
WillieD24 (rep: 557) Mar 18, '22 at 11:10 pm
Willie. Your code works well and has solved a problem for at least two people (though it looks like El99 won't be confirming that or raising your Reputation on the Forum, sadly). I was only suggesting a refinement to your good work.
John_Ru (rep: 6142) Mar 19, '22 at 2:57 am