Enabling autosave when starting Excel

Trev143

New Member
Joined
Mar 2, 2017
Messages
6
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
This should work for you. I have it to save on exit. I found it somewhere around here as well.

Code:
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
 
Upvote 0
Sorry, I have you the wrong code. that one is for always saving into macro workook.

this is the one for saving:

Code:
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
 
Upvote 0
'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.
 
Upvote 0
If you're referring to the AutoRecover feature then it should remember you settings.
So if you enable AutoRecover, it should be permanently enabled.
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
Let's try again:

1) When starting Excel
Om3blEf


2) After changing the options:
Whr0vPI


Hope you can see the images.
 
Upvote 0
What version of Xl are you running?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top