Self Saving Work Book After One min

[adamsm]

Hi anyone,

How could I write VBA code that would save the workbook automatically each min without the user having to save manually?

Any help on this would be kindly appreciated.

Thanks in advance.

 

[VoG]

Try this: in a regular module

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "MyMacro"
ThisWorkbook.Save
End Sub

In the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "MyMacro"
End Sub

Save, close and re-open the workbook to test.

 

[adamsm]

Thanks for the reply. How could I modify the code so that each time the workbook gets saved. it displays the saved time in "MySheet" cell A2.

by the way when im trying to close the workbook im getting the debug message 1004
highlighting the line

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
End Sub

How can I avoid this and why is this happening?

Any help on this would be kindly appreciated.

 

[VoG]

Try this

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:01:00")
Application.OnTime dTime, "MyMacro"
ThisWorkbook.Save
Sheets("MySheet").Range("A2").Value = Now
End Sub

To prevent the error when trying to close, in the code window press CTRL + G then in the Immediate Window type

Application.EnableEvents=False

and press Enter. Save and close the file. Go back to the Immediate window and type

Application.EnableEvents=False

and press Enter.

 

[adamsm]

Thanks for the reply. Will work on that and let you know