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

possible duplicate of Delay Excel save function and How to have vba execute every 10 minutes? — Dmitry Pavliv
– Dmitry Pavliv, Commented Jun 10, 2014 at 9:54

Tom R · Accepted Answer · 2014-06-10 10:35:42Z

2

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.

edited Jun 10, 2014 at 10:35

answered Jun 10, 2014 at 10:29

Tom R

687 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Timeout in Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related