Auto Save Macro

[mackem]

i want to have a macro which will autosave the worksheet/book automatically every minute so the user doesnt have to keep clicking file and save.

[Dave Hawley]

Bad idea as you'll sooner or later save something that shouldn't be. E.g Worksheet deletion.

[mackem]

Hi Dave i understand what you mean re: saving something didnt want to but worksheets are protected so should be ok can the following be done though? " i want to have a macro which will autosave the worksheet/ book automatically every minute so the user doesnt have to keep clicking file and save".

Cheers Graeme

[Dave Hawley]

Ok, but don't say you were not warned and Sheet protection wont stop sheet deletion. To stop sheet deletion protect the Workbook and ensure you check Structure.

Right click on the Excel icon, top left next to File, choose View Code and in here paste;

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Application.OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll", , False 
End Sub 
 
Private Sub Workbook_Open() 
    DTime = Time 
    Application.OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll" 
End Sub 

Now go to Insert>Module and paste in this;

Public DTime As Date 
 
Sub SaveMe_MistakesandAll() 
    DTime = Time 
    Application.OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll" 
    ThisWorkbook.Save 
End Sub 

Save, close and re-open with macros enabled and it should save every minute.

[PCI]

After running the macros, here some additional information.
Change

Application .OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll", , False 

to

Application.OnTime DTime + TimeValue("00:01:00"), "SaveMe_MistakesandAll", False 

Only one ","

In the module add the SAVE statement

Sub SaveMe_MistakesandAll() 
    DTime = Time 
    Application.OnTime DTime + TimeValue("00:00:07"), "SaveMe_MistakesandAll" 
    ActiveWorkbook.Save 
End Sub 

There is an additional issue: If you close the file and NOT EXCEL the file is opened again.

[Dave Hawley]

The correct syntax is: expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule) and it's the that must be set to False upon closing, not the LatestTime.

Other than missing out ThisWorkbook.Save in the SaveMe_MistakesandAll Procedure, my code is correct.

[PCI]

Completely clear now, perhaps the Copy / Paste made was not good, the 2 "," are needed without anything between.
The Master is still excellent.

[mackem]

sorry dave have tried these but continues to say ambiguous argument or words to that effect and if i switch off without saving after 5 mins things still dont save?

[PCI]

Mackem,
Find attached a file with macros as shown by Dave in works with a delay of 15s for testing reason.

[Dave Hawley]

sorry dave have tried these but continues to say ambiguous argument or words to that effect and if i switch off without saving after 5 mins things still dont save?

You must have 2 Procedures using the same name.