Autosave every 5 minutes

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,200

Office Version

1. 365
2. 2010

Platform

1. Windows

• Aug 8, 2014

• #2

sharky12345 said:

Can someone give me an example of how I can autosave a workbook every 5 minutes but include a message box advising of such? The message box should appear in front of anything that is going on, including userforms.

Click to expand...

This can be tied to Workbook_Open and Workbook_BeforeClose events to call SaveIt when the workbook opens and call StopSaveIt when you close the workbook.

Code:

Public RunWhen
Sub SaveIt()
RunWhen = Now + TimeSerial(0, 5, 0)
Application.OnTime RunWhen, "saveit", , True
ThisWorkbook.Save
MsgBox "Your file " & ThisWorkbook.Name & " was just autosaved"
End Sub
Sub StopSaveIt()
On Error Resume Next
Application.OnTime RunWhen, "saveit", , False
End Sub

 

Upvote 0

sharky12345

Well-known Member

Joined

Aug 5, 2010

Messages

3,404

Office Version

1. 2016

Platform

1. Windows

• Aug 8, 2014

• #3

Thank you - can I just clarify, does your code go in both the open and close events or one in each other and if so which?

 

Upvote 0

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,200

Office Version

1. 365
2. 2010

Platform

1. Windows

• Aug 8, 2014

• #4

sharky12345 said:

Thank you - can I just clarify, does your code go in both the open and close events or one in each other and if so which?

Click to expand...

The code I posted goes in a standard module in the workbook you want to auto save. Then in a Thisworkbook module you would have:

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopSaveIt
End Sub

Private Sub Workbook_Open()
Call SaveIt
End Sub

 

Upvote 0

sharky12345

Well-known Member

Joined

Aug 5, 2010

Messages

3,404

Office Version

1. 2016

Platform

1. Windows

• Aug 8, 2014

• #5

Thanks!

 

Upvote 0

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,200

Office Version

1. 365
2. 2010

Platform

1. Windows

• Aug 8, 2014

• #6

sharky12345 said:

Thanks!

Click to expand...

You are welcome - I'd be interested in knowing how many appearances of that message box it takes to exceed the tolerance of you or your users .

 

Upvote 0

sharky12345

Well-known Member

Joined

Aug 5, 2010

Messages

3,404

Office Version

1. 2016

Platform

1. Windows

• Aug 8, 2014

• #7

Haha - I told them that every 10 minutes would be bad enough, but now they want it at 3 minutes, I'll let you know when they ask for it to be extended, I guess within the next 10 minutes!

 

Upvote 0

sharky12345

Well-known Member

Joined

Aug 5, 2010

Messages

3,404

Office Version

1. 2016

Platform

1. Windows

• Aug 8, 2014

• #8

Slight problem now - I'm getting a run time error on closing, error number 1004 - 'Method 'OnTime' of object '_Application' failed....any ideas?

 

Upvote 0

sharky12345

Well-known Member

Joined

Aug 5, 2010

Messages

3,404

Office Version

1. 2016

Platform

1. Windows

• Aug 10, 2014

• #9

*bump*

 

Upvote 0

J

JoeMo

MrExcel MVP

Joined

May 26, 2009

Messages

18,200

Office Version

1. 365
2. 2010

Platform

1. Windows

• Aug 10, 2014

• #10

sharky12345 said:

*bump*

Click to expand...

Did you modify the code I posted in any way? Do you have any other workbook_beforeClose events in Thisworkbook or any other code in place that might interfere?

I am unable to reproduce the problem.

 

Upvote 0