Execel file auto save

[Sam]

Hi,

Is there any way that an excel sheet automatically saves the changes
everytime some/any modification is done?
I am not asking about the auto save option (which is selected after sharing
the workbook) which saves the sheet every 5 min (or any specified time).

 

[Jacob Skaria]

If you mean to save as soon as a change is made then try the below code. Set
the security level to low/medium in (Tools|Macro|Security). From workbook
press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview
search for the workbook name and click on + to expand it. Within that you
should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ThisWorkbook.Saved = False Then ThisWorkbook.Save
End Sub

'or if you want to save just before close...try the below

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = False Then ThisWorkbook.Save
End Sub


If this post helps click Yes