Larry Steinle

For many business users Excel remains a very important tool for business use. Sometimes the Excel spreadsheet may need to be shared among many business users. As long as everyone plays nice taking care to close the worksheet when they are done it’s not a problem. But, as you know, in our busy work-schedules it is very easy to get distracted forgetting to close the worksheet. What is required is a way to automatically save-and-close an Excel Spreadsheet after a specified amount of time.

I actually started my career in Visual Basic programming showing companies how they could use products like Microsoft Excel and Microsoft Access to automate business processes. We used Visual Basic for Applications (VBA) extensively to solve some very real world business problems. From there I jumped into Visual Basic 4 and progressed into VB.Net and C#.Net.

Occasionally someone requests help with a problem related to these technologies. Always happy to lend a helpful hand I go back to my roots and assist. Today’s article actually arose from one such encounter. After helping to fix the code I thought it worthwhile to share because many business users continue to rely heavily on the Microsoft Office product family.

This one’s for you die-hard expert business users!

Using a Timer to Decide When to Save and Close the Workbook

First open up Excel and click Alt+F11 to open the VBA code window. Begin by creating a new module and in the Properties frame set the Name to AutoClose. Add the following code to the module and save your changes.

Public SaveAndCloseAfter

Sub StartTimer()
    SaveAndCloseAfter = Now + TimeValue("00:30:00")
    Application.OnTime EarliestTime:=SaveAndCloseAfter, Procedure:="AutoClose.CloseWB", Schedule:=True
End Sub

Sub StopTimer()
    If SaveAndCloseAfter Then
        Application.OnTime EarliestTime:=SaveAndCloseAfter, Procedure:="AutoClose.CloseWB", Schedule:=False
        SaveAndCloseAfter = Empty
    End If
End Sub

Sub ResetTimer()
    StopTimer
    RunTimer
End Sub

Sub CloseWB()
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    ThisWorkbook.Close
End Sub

The variable, SaveAndCloseAfter, tells the system at what time to save and close the workbook. When the timer is started this value is set to thirty minutes in the future. The Application.OnTime command tells Excel what function to run after the 30-minute interval has passed. Of course if you want to save-and-close your workbook after 10 minutes this is the line of code to change.

Next we provide a function to stop the timer and clear out the SaveAndCloseAfter date value. To make things easier we have a ResetTimer function that stops and restarts the timer setting it to run in another thirty minutes.

The CloseWB routine will get called when the timer expires. This method disables any alerts and message boxes so that the save can occur without any user intervention and the workbook can close.

The AutoClose module provides the mechanics necessary to support automatically saving and closing a workbook after thirty minutes of inactivity. Now we simply need call the appropriate methods.

Using Workbook Events to Manage the Timer

In the project window under the Microsoft Excel Objects folder, double-click the ThisWorkbook module to open it adding the following code:

Private Sub Workbook_Open()
    AutoClose.StartTimer
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    AutoClose.ResetTimer
End Sub

When the workbook is opened the timer will begin immediately. Thirty minutes of inactivity and the timer will fire the CloseWB function. But to keep the workbook from closing when the user is active we have to reset the timer in the Worksheet_Change event.

Summary

Voila! Nothing too fancy but sure can be helpful when you need it. Now we can automatically save and close a workbook!

Happy coding!