Changed or Updated property?

[Guest]

Hi All,

Is there any way to check if any cells in a worksheet has been changed? I
want to use a Worksheet_Deactivate() event, but only if there was a change in
the sheet to avoid lengthy processing.

Thanks,
Stefi

 

[Tom Ogilvy]

You could save the file on the activate event (or change Thisworkbook.Saved
= true). then in the deactivate event, you could check Thisworkbook.Saved.
If true, no change. If false, there has been a change. [Lightly tested]

Other than that you would have to detect the change yourself (possibly using
the change event).

Note that changing Thisworkbook.Saved can affect the prompt to save before
closing.

 

[Guest]

Hi Stefi,

This may work, but you have to use an intermediate cell somewhere to store
the status of the sheet:

(macro's in sheet1)
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Range("a1").Value = True
End Sub

Private Sub Worksheet_Deactivate()
If Sheet1.Range("a1").Value Then
MsgBox "Sheet has been changed"
Else
End If
End Sub

Regards,
Excelerate
Jan Bart

 

[Guest]

Sorry,

First reply is wrong. Use This instead:

You should use another sheet to store the data

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet2.Range("a1").Value = True
End Sub

Private Sub Worksheet_Deactivate()
If Sheet2.Range("a1").Value Then
MsgBox "Sheet has been changed"
Sheet2.Range("a1").Value = False
Else
End If
End Sub

 

[Guest]

Many thanks to both of you Tom and Jan, both solution are clever workarounds!
Nonetheless I suggest Microsoft to build in a new "Updated" property in some
later version!

Regards,
Stefi