Autosave after cell is modified

[sprucethebruce]

I've tried my usual google search and still at a loss, so.....

Mr Excel too the rescue, hopefully.

I want a spreadsheet to automatically save when any of the cells in the range P14:P95 are edited.

Anyone?

 

[shg]

Are you sure about that? If someone makes a mistake, it gets instantly immortalized.

Code goes in the Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("P14:P95")) Is Nothing Then
        ThisWorkbook.Save
    End If
End Sub

 

[sprucethebruce]

Thanks shg,

Immortilisation is my aim, I'm a sadist.

Not really, its a spreadsheet that records work completed, the cells in question have a drop down list and are used to confirm when a job is completed. Operators are not the best at using the save button so this will mean they can forget but their work wont be forgotten.

 

[Nalani]

If your intention is just to get the Workbook saved in case the user forgets, then maybe this in the ThisWorkbbok Module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    '//Turn off alerts
    Application.DisplayAlerts = False
       '//Save the workbook without asking for approval
    Application.ActiveWorkbook.Save
End Sub

shg's code will activate on Every change of the range P14:P95.

After awhile I would think your users might not want to use the workbook expecially if they are making multiple changes in that range.

 

[sprucethebruce]

Thanks Nalani,

The column in question should only be edited once every hour so it shouldn't effect the operation of the spreadsheet.

I believe your code will only work if the spreadsheet is closed, something the operators do not do.

Its a good bit of code which I may use on other spreadsheet though.

 

[Nalani]

After awhile I would think your users might not want to use the workbook expecially if they are making multiple changes in that range.

Just wanted to clarify my statement.

Nothing at all wrong with sgh's code if the Workbook is small.

But if it is a large Book the user will have to wait for the Save to complete on each change of the range.<!-- / message --><!-- sig -->

 

[sprucethebruce]

Its not a large book, the data is archived daily too so it doesn't expand in size.

 

[Nalani]

I believe your code will only work if the spreadsheet is closed, something the operators do not do.

So is this a Shared Workbook, since you use the Plural and also state that they leave it open?

 

[sprucethebruce]

Yes Nalani, its shared.

 

[Nalani]

Then shg's code would be more suitable.

I'm just not to sure, or experienced with Shared Books, that the other users would see the "saved" change made by the Change_Event if more than one user had the Book open at the same time.

Maybe shg knows that answer.

Or better yet, you could test that scenerio yourself.