VBA Saving delay after data entry

Question

VBA newbie here. I am looking for a way that will allow my excel file to automatically save after a delay of 1 min after data input.

For example:

User Inputs Data --> Timer Starts (1min)

5 seconds passes.

User inputs Data --> Timer Restarts (1min)

1 min passes.

Excel File Saves - until the user starts inputting data again

Any thoughts?

Answer 1

One possibility is to leverage the Workbook.SheetChange event and Application.OnTime. You'll also need a Public variable, ScheduledTime in the example below.

Every time any (non-chart) sheet is changed (e.g. via data entry):

1. Any previously scheduled save, as long as it's still within the one-minute window, is cancelled.
2. A new save is scheduled for one minute out.

---

So something like the following:

In the ThisWorkbook code module:

Option Explicit

Public ScheduledTime

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

    On Error Resume Next
    Application.OnTime EarliestTime:=ScheduledTime, Procedure:="SaveTheFile", Schedule:=False
    On Error GoTo 0

    ScheduledTime = Now + TimeValue("00:01:00")
    Application.OnTime EarliestTime:=ScheduledTime, Procedure:="SaveTheFile"
End Sub

In a regular code module:

Public Sub SaveTheFile()
    ThisWorkbook.Save
End Sub

---

You could just as well use the Worksheet Change event if you want to restrict this to a particular sheet.