Sensing altered workbook

[dsimcox]

Is there a way to determine if anything in a workbook has changed since it was opened?

I would like to use this information to run an On Close event before the workbook is closed - but only if the workbook has been altered.

 

[BobUmlas]

Create a public variable, like
Public Changed as Boolean
and in the code for the ThisWorkbook object, do something like:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Changed = True
End Sub
In the BeforeClose event, test the value of Changed:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Changed then
'do your thing here
End If
End Sub

 

[Joe Was]

If you Share the Workbook on a Network Server you can compare the current Server copy to the Active copy, for any changes:


Dim objSync As Office.Sync
Dim strStatus As String

Set objSync = ActiveDocument.Sync

If objSync.Status = msoSyncStatusConflict Then
strStatus = "The server copy has been changed." & vbCrLf & _
"Changes have been made by: " & _
objSync.WorkspaceLastChangedBy

MsgBox strStatus, vbInformation + vbOKOnly, "Server Copy Changed"
End If

Set objSync = Nothing

 

[dsimcox]

Thank you gentlemen . . .

Your solution worked beautifully, Bob.

I'm going to experiment with Sharing thw workbook on our server - I've never dabbled with the .Sync method - I need to understand it, Joe.

Appreciate the expert help!