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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

elynoy

Board Regular
Joined
Oct 29, 2018
Messages
124
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
 

elynoy

Board Regular
Joined
Oct 29, 2018
Messages
124
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
 

Trev143

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Oct 29, 2018
Messages
124
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

New Member
Joined
Mar 2, 2017
Messages
6

ADVERTISEMENT

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:

https://imgur.com/Om3blEf

This is what I have once I have changed the options

https://imgur.com/Whr0vPI

Is there some code available that changes the one into the other?

<tbody>
</tbody>

<tbody>
</tbody>
 

Trev143

New Member
Joined
Mar 2, 2017
Messages
6
Let's try again:

1) When starting Excel
Om3blEf


2) After changing the options:
Whr0vPI


Hope you can see the images.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
What version of Xl are you running?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,580
Messages
5,637,211
Members
416,961
Latest member
sigrid6940

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
Top