Code to track changes in an excel file

You can do this with built-in Tracked Changes - there is an option to list changes on a new sheet.

Hi Stephen,
that looks perfect but each time I open the file I again have to adjust the settings to get the result. The "history sheet" as such is perfect.
Now I need some help to make my settings constant so the history sheet will stay after re opening and history will be added and added and added....

It there a way to keep it working once set?

Nils - the history sheet will stay and add your changes each time but you need to untick the when/who/where boxes. If you mean without manual intervention, then yes it doesn't appear to work and macros cannot be used in shared workbooks. I have not really dabbled with this. Code could probably be written, I will have a look.

Nils - try this in the sheet1 module:

Dim vOld

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:Z100")) Is Nothing Then vOld = Target
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

With Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
    .Value = Now
    .Offset(, 1).Value = Environ("username")
    .Offset(, 2).Value = Target.Address
    .Offset(, 3).Value = vOld
    .Offset(, 4).Value = Target
End With

End Sub

Select allOpen in new window

Please add this at line 8:

If Intersect(Target, Range("A1:Z100")) Is Nothing Then Exit Sub

Select allOpen in new window

Hi, I will try it tomorrow...
just to let you know....the file where I want to track the history will not be used as shared file.
I will just be edited by one or two people with access rights to that file and only one or the other person will edit it.
We will have 5 of this files, each in a different folder with specific access rights to a limited amount of people and in the 6 folder we will have a "summary" file where we will consolidate the information of this 5 files into one file.
In this way person A has the full overview but does not need to collect all data and person 1 to 5 collect their part in their file.
To ease for the "boss" what happened where I had the idea to track changes in the files 1 to five in that extra sheet and consolidate this information as well in the "master file"......

The code you have written does replace the build in tracking function right?
thanks
Nils

Hi Stephen JR,
shall I add the code to the sheet and/or as a new module?
What should happen? Somehow now reaction at all....
thanks for help...maybe sample file....thanks
Nils

Add it to sheet1's module - right-click sheet tab, view code and paste the code. yes post a sample if still in doubt.

Hi Stephen,
the problem was that I did not look to sheet2 :-) but looked for a "history" sheet....

it works as I need :-) thanks a lot.

Small adaptation...If I want to have this working for sheet1 I add your code to sheet one and on sheet2 I get the history...perfect...what I have to do if I want the history on sheet10? Change this in this way?

With Sheet10.Range("A" & Rows.Count).End(xlUp)(2)

And what I have to do/adapt If I want such a function for sheet1 and sheet2 and sheet3? Copy three times your code to the sheets and define where to get the history results for 1, 2 and 3 respectively in each code?
thanks
Nils

perfect!
thanks a lot
Nils