Timeout in Excel VBA

Question

I was wondering if it's possible to create a VBA macro in Excel, which will save the file each X minutes. I've already figured how to initialize the macro after excel startup and I found on the google something like this should pause the macro

Application.Wait(Now + TimeValue("0:10:00"))

but this will also block the user input and he cannot make any changes during that time.

This is not a anti-crash protection , the reason why I need this, is that some users are forgetting to save the document regularly...

Thank you Francis

Answer 1

The 2 examples of Simoco will work great, but if you want to prevent having to deal with unnecessary saves (especially if you're working on network files, or large files), you can do a check everytime there is a change in the worksheet.

Just use the Worksheet_Change function to do that, here is a possible pattern:

Private Sub Worksheet_Change(ByVal Target As Range)


If (Now > TimeStamp)
    ThisWorkbook.SaveAs BlaBlaBlaBlaBla
    TimeStamp = Now + TimeValue("0:10:00")
End If

End Sub

TimeStamp needs to be a global variable defined in each workbook.

Btw, make sure that saving your file every X minutes doesn't screw with the undo / redo function of excel. I remember I had unwanted behaviors in the past when using an auto-save function.

Other thought: Google document won't require this type of macro as there is no need to save.