VBA to autosave and close after a set amount of time

ceranes

New Member
Joined
Jan 19, 2018
Messages
12
We have the same problem at work as I'm sure many others have had. Someone opens the shared spreadsheet file, then jumps on a conference call, leaves the office, and forgot they left it open. Several hours pass and nobody is able to do any work in the file.

I asked our girl in IT about this, she gave me some VBA, but she is not really versed in it. She claims it works on her computer, but I am unable to get it to work on mine. I am using Excel 365. Others in our office either use that version or a version much older, such as Excel 2016 or even 2013.

This is really a two-part question.

First I copied a test version onto my desktop and saved it as a macro-enabled file (.xlsm).

Next, I followed her instructions to a tee.

First, I opened the workbook and then the Visual Basic Editor by clicking the Developer tab and then Visual Basic. Next I click the Insert tab and selected Module. I entered the following code.

VBA 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

Next I double-clicked on ThisWorkbook, and entered the following code.

VBA Code:
style='font-family:inherit;color:#141414'>Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 
StartTimer
 
End Sub

According to the lady I spoke with, after 10 minutes of inactivity, it would automatically save and close the file. I modified the time for a single minute (60 seconds), and it just sat there.

Does anybody see anything wrong with the VBA?

The second part of this issue is how do you force Excel to automatically run the VBA content without having to prompt someone to accept it? If we can't get someone to remember to save and close a file when they're finished, they're sure as not going to click a button to accept a macro. They'll just click off it.

Thanks for your time!

Chris
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,797
.
VBA Code:
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

Download workbook : Auto Close After N min of Inactivity.xlsm



Here is a simpler version :

Code:
Option Explicit

Const idleTime = 60 'seconds If NO activity of any kind occurs within 60 seconds, WB closes and auto saves.
Dim Start
Sub StartTimer()
    Start = Timer
    Do While Timer < Start + idleTime
        DoEvents
    Loop
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
      
    
    Application.Quit
    ActiveWorkbook.Close SaveChanges:=True
    
    Application.DisplayAlerts = True
End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Mustafa Evans

New Member
Joined
Mar 18, 2010
Messages
35
Office Version
  1. 2010
Platform
  1. Windows
I really like this idea of auto closing and saving after a certain amount of time. I'm having issues with a shared spreadsheet with users often leaving them open for hours at a time. What I don't understand however is what code do I put where. And was the issue of making the user click enable macro resolved.

Would really appreciate your help with this.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,797
The end user must have MACROS ENABLED on their copy of EXCEL.

To make the "time out" macro auto-enabled, place a "call" to the macro in the ThisWorkbook module Workbook_Open macro.

For example :

Code:
Option Explicit

Private Sub Workbook_Open()

     StartTimer

End Sub

Then, the actual macro StartTimer needs to be pasted into a Regular module :

Code:
Option Explicit

Const idleTime = 60 'seconds If NO activity of any kind occurs within 60 seconds, WB closes and auto saves.

Dim Start

Sub StartTimer()

    Start = Timer

    Do While Timer < Start + idleTime

        DoEvents

    Loop

    Application.DisplayAlerts = False

    Application.ScreenUpdating = False

    Application.Quit

    ActiveWorkbook.Close SaveChanges:=True

    Application.DisplayAlerts = True

End Sub

This allows the macro to auto-run each time the workbook is open - doesn't need the end user to activate the macro and .... for most end users, they won't even
know the macro is there or running.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,746
Messages
5,573,986
Members
412,562
Latest member
woodportaj
Top