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.

 

[Logit]

Paste in ThisWorkbook module :

Option Explicit

Private Sub Workbook_Open()
    StartTimer
End Sub

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

Paste into its own module :

Option Explicit

Const idleTime = 30 'seconds
Dim Start
Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
'///////////////////////////////////////////////////////
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    'Step 1: Declare your variables
    Dim ws As Worksheet
    'Step 2: Unhide the Starting Sheet
    Sheets("Sheet1").Visible = xlSheetVisible
    'Step 3: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
    'Step 4: Check each worksheet name
    If ws.Name <> "Sheet1" Then
    'Step 5: Hide the sheet
    ws.Visible = xlVeryHidden
    End If
    'Step 6: Loop to next worksheet
    Next ws
    'Application.ScreenUpdating = True
    
    Range("A1").Select
    
    ThisWorkbook.Save
    
    'Application.DisplayAlerts = True
'//////////////////////////////////////////////////////////
    'Application.DisplayAlerts = False
    Application.Quit
    ActiveWorkbook.Close SaveChanges:=True
    
    Application.DisplayAlerts = True
End Sub

 

[fdirosas]

Hello Logit,

I've tried your script with a few changes (only save, don't close workbook), but did nothing. I'm using Excel 2019 with macro enabled workbook (no AutoSave feature which I keep getting in google search). This file will also be shared. I want to save every X amount of time without closing the workbook. Also, I don't mean to use AutoRecover feature (which I keep getting in google search as well). Here are the changes I did to your VBA script below:

'//////////////////////////////////////////////////////////
'Application.DisplayAlerts = False
Application.Quit
ActiveWorkbook.Close SaveChanges:=True --> ActiveWorkbook.Save


Application.DisplayAlerts = True
End Sub

How to get it to save automatically in Excel 2019 please. I spent lots of time trying to find a solution to no avail.

 

[Logit]

Here is the file I am using with macros shown above. It auto saves after 30 seconds of inactivity. You can adjust the time as described in the macro code.
If you are attempting to use this code on a Google file ... I am not certain if it will work ... or not. I've never tried it on a Google file.

Internxt Drive – Private & Secure Cloud Storage

 

[fdirosas]

It working thank you!

 

[Logit]

You are welcome.

 

[fdirosas]

Sorry spoke too soon. I'm back to getting this error message "This workbook contains macros recorded or written in Visual Basic. Macros cannot be viewed or edited in shared workbooks." then it sopped saving.

 

[Logit]

Unshare the workbook. Main menu up top.

 

[fdirosas]

Unshared works, but I need it to be shared. This is where I encounter the automatic saving issue. If this is not possible without any workaround, I'm ok with that too. Just don't know if it's possible or not.

 

[Logit]

Sugestion: When the user accesses the workbook, place it in 'Unshare' status. Add to the macro additional code to place the workbook back in shared status just before closing. Also, shorten the
wait time before the workbok automatically closes. 1 hour is a long time to have a file just sitting there. I believe five minutes or less would be more reasonable to achieve the goal you are seeking.
As the users get use to the 'auto close' feature, their use of the computer will become more efficient.