Enabling autosave when starting Excel

[Trev143]

It can be enabled manually with tools - autosave and then clicking the appropriate radio buttons. But you have to remember to do it, and it lasts only until you quit Excel. Is there a way of having it turned on permanently, or of enabling it by using VBA? I tried recording a macro but it gave me just an empty sheet. I guess it's not encouraged - at least by Microsoft - owing to the danger of saving changes that you don't want saved, but I find that the danger of losing work is greater.

 

[elynoy]

This should work for you. I have it to save on exit. I found it somewhere around here as well.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim fName As Variant
    On Error GoTo ErrorHandler
    If SaveAsUI Then
        Cancel = True   'Cancel the original SaveAs
         'Get filename (with path) for saving
        fName = Application.GetSaveAsFilename(FileFilter:="Excel Marcro-Enabled Workbook (*.xlsm),*.xlsm")
        If fName = False Then Exit Sub  'Exit if user hit Cancel
        Application.EnableEvents = False  'Prevent this event from firing
        ThisWorkbook.SaveAs Filename:=fName, FileFormat:=52
          '52 = xlOpenXMLWorkbookMacroEnabled = xlsm (with macro's in 2007-2010)
        Application.EnableEvents = True  'Re-enable events
    End If
Exit Sub
ErrorHandler:
    Application.EnableEvents = True   'So events are never left disabled.
    MsgBox "An error occured during save." & Err.Number, vbCritical, "Error"
End Sub

Best regards,
eLy

 

[elynoy]

Sorry, I have you the wrong code. that one is for always saving into macro workook.

this is the one for saving:

Private Sub Workbook_BeforeClose(Cancel As Boolean)


    ActiveWorkbook.Save


End Sub

so, even if you forget to save it should save while exiting the workbook.

sorry again for the mess up but I cant change that code now.

eLy

 

[Trev143]

'fraid not. I copied both into new workbooks but when I clicked on tools - macros there was nothing to run. Nor did trying to step through with F8 do anything. What am I doing wrong?

Though what I want is not to save on exit but to save the open workbooks automatically at 10 minute intervals. This is something I can set with tools - autosave but I'd like to have it switched on automatically when I start Excel.

 

[Fluff]

If you're referring to the AutoRecover feature then it should remember you settings.
So if you enable AutoRecover, it should be permanently enabled.

 

[elynoy]

Remove the "private" from the sub on the code. I have it private for my own project. If you remove the private it will show on the macros to run.

eLY

 

[Trev143]

Had to find out how to add an image with a URL. But here it is.

I asked the question wondering if there was something in the Excel settings that could be captured.

This is what I have when I start:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]https://imgur.com/Om3blEf

This is what I have once I have changed the options

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]https://imgur.com/Whr0vPI

Is there some code available that changes the one into the other?[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Trev143]

Let's try again:

1) When starting Excel

2) After changing the options:

Hope you can see the images.

 

[Fluff]

What version of Xl are you running?

 

[Trev143]

2000, vn 9.0.2070