VBA to automatically save and close an Excel file after a period of inactivity in the file

[VANCOUVER_RON]

I want to automatically close an Excel file after a period of inactivity. This was helpful as a start, but...

How to save and close workbook after inactivity for a certain amount of time?

Learn how to automatically save and close an Excel workbook after a period of inactivity using VBA code.

www.extendoffice.com

Not precisely what I'm looking for. It appears the ''timer" starts running when you open the Excel file and doesn't stop. Then the "ballistic" code saves and closes the file
automatically after the timer runs out, even if you're actively using the Excel file.

I need to "reset" the timer every time the user is actively using the Excel file--inserting data, updating a form--doing anything in the file resets the timer. It's only after a
period of time of inactivity (e.g., one hour of inactivity) that I want the "shut down" timer to begin. For example, if the file is inactive for an hour, then the timer
(e.g., 30 seconds) begins running and the file automatically closes and saves after the 30 second timer runs out.

In other words, activity in the file continually resets the shut-down timer. That's what I'm looking for.

 

[fdirosas]

Well, that would work, except the computer which shows the spreadsheet on the big TV screen will have to remain Shared. Otherwise, if that PC was moved to one of the worker's desks, then constantly, other workers would request to Share the file so then other workers can update, save then close.

I have another idea,

If the timer does not work in shared workbooks, then can the file save after every edit of the spreadsheet? The updates are usually updates to one to two cells so it will not be many. Can this be a MACRO in "ThisWorkbook" that can accomplish this?

 

[fdirosas]

Actually it's shared because a PC is used to show the spreadsheet on the big screen.

I have an idea, can a MACRO be used while shared to save every time any cell gets edited?

 

[fdirosas]

I found the solution to incorporate sorting and saving (which both had the same issue when using timers in shared workbook). This will not only save on each edit, it will also resort then save again to auto-sort in shared file (it works):

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

ThisWorkbook.Save

ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("O4:O1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ThisWorkbook.Save

End Sub

 

[Logit]

Glad you found your solution.