CODEWITHSUNDEEP
excelvbasavedata-entry
Newbie
Newbie

Reputation: 31

VBA Saving delay after data entry

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?

Upvotes: 0

Views: 322

Answers (2)

Diederik Sieburgh
Diederik Sieburgh

Reputation: 111

I have a similar take on this to BigBen.

In ThisWorkbook module:

Option Explicit

Public SnapShot As String

Private Sub Workbook_Open()
    StartTimer
End Sub

Sub StartTimer()
    If SnapShot = vbNullString Then SnapShot = Now
    If DateDiff("s", SnapShot, VBA.CStr(Now)) >= 10 Then ThisWorkbook.Save
    RestartTimer
End Sub

Sub RestartTimer()
    Application.OnTime Now + TimeValue("00:00:10"), "ThisWorkbook.StartTimer"
End Sub

And then in the worksheet you are monitoring: 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.SnapShot = Now
End Sub

Upvotes: 0

BigBen
BigBen

Reputation: 49998

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.

Upvotes: 2

Related Questions