Tracking Changes in a non-shared workbook

[Andrew]

Is there any macro that could track changes in an Excel workbook without the
workbook being shared? I came across the following macro:

Dim nFile
nFile = FreeFile
Open "C:\MyLog.txt" For Append As #nFile
Print #nFile, "Workbook " & ThisWorkbook.Path & _
" opened by " & Environ("UserName") & _
" on " & Format(Now, "yyyy/mm/dd hh:mm:ss")
Close #nFile

which saves a list of when the file is accessed. But I've no idea how to
amend this to list any changes to the the workbook. In actual fact, it's
only one specific sheet that I need to track, however the nature of the file
won't allow me to share it.

Any help would be much appreciated.

Andrew.

 

[Dave Peterson]

Maybe you could tie into the worksheet_change event and add some more text (each
cell being changed--it's address and new value) and put them into your text
file, too.

But remember that if the user closes the workbook without saving, your text file
with those changes doesn't make sense.

It might make more sense to keep track of the changes in (multiple??) worksheets
stored within the same workbook.

Something like this may give you a start if you want to do that.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestCell As Range
Dim LogWks As Worksheet
Dim myCell As Range
Set LogWks = Worksheets("logsheet")

If Intersect(Target, Me.UsedRange) Is Nothing Then
Exit Sub
End If

With LogWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

For Each myCell In Intersect(Target, Me.UsedRange).Cells
DestCell.Value = Application.UserName
With DestCell.Offset(0, 1)
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
DestCell.Offset(0, 2).Value = myCell.Address(0, 0)
With DestCell.Offset(0, 3)
.NumberFormat = "@"
.Value = myCell.Value
End With
With DestCell.Offset(0, 4)
.NumberFormat = "@"
.Value = myCell.Formula
End With
Set DestCell = DestCell.Offset(1, 0)
Next myCell

End Sub