Auto Save

[tqm1]

Dear Experts

I want my Workbook to save automatically when any changes is made.

Please help

[Bob Phillips]

This is total overkill IMO.



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Me.Save
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[tqm1]

It says
Method or data member not found

[Bob Phillips]

Not if you put it where I told you to.

[nedy_03]

Just looking by .. it perfectly works! Nice tip!

[mfegyver]

changing a bit from topic,
Q1-how this event driven method start to work? is it due to the "private sub" or due to ByVal ?

Q2-what is this Sh and why have to define it as object and what means Target and why defined as range?

I startet with this code:Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)

If Cells(10, 10) = "X" Then
Me.Save
Else
Cells(10, 10) = "Y"
End If
End Sub


many thanks and "saludos" from brasil

[Bob Phillips]

Q1 it is neither of those. It is because it is a built-in event that is exposed to VBA by Excel.

Q2 Sh is just a name, it could be anything you want, but Sh just makes sense. It is Object because it is an object. I have no idea why it is not Worksheet rather than object, but VBA objects if you change it, as it is only a worksheet that this event applies to. I guess that other events like Activate also appliy to Charts, so they cannot be Worksheet but have to be Object, so it is being consistent.

Target is the cell that is changed, and is type Raange because cells are ranges.

[Bob Phillips]

Oh yes, and you should have started your own thread, it is nothing to do with this one.

[nedy_03]

i tried
Private Sub Workbook_BeforeClose(ByVal Sh As Object, ByVal Target As Range)
Me.Save
End Sub

i want to autosave before closing the workbook, but it didn't work out. Can u help me on taht?..

thx,
Nedy

[johnske]

wrong arguments, this is event driven code and Excel determines what the (predefined) arguments are, you will get errors if you change them from that...try

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then Me.Save
End Sub
and try starting your own thread in future :)

[nedy_03]

Thx

[nedy_03]

And is it possible to write a code that saves only before closing the worksheet ... and doesn't allow to save unless u choose to close the worksheet ?

[johnske]

Option Explicit

Public ClosingBook As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ClosingBook = True
If Not Me.Saved Then Me.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not ClosingBook Then Cancel = True
End Sub

[nedy_03]

Thx