Code to track changes in an excel file

Petersburg1
Petersburg1 used Ask the Experts™
on
Dear Experts,
I'm looking for a most likely vba solution in order to track changes made within a defined range of cells.
At sheet1 In the range A1:Z100 I want to track changes made.
At sheet2 I want to track the changes in the following way:

date      author # cel old value new value

If user NIK does change the value ABB in A15 to BBB and the value in cell B17 from xxx to yyy etc. this should be listed in shee2 respectively:

01-Sept-11, NIK, A15, ABB BBB
01-Sept-11, NIK, B17, xxx, yyy

thank you in advance for help, advice and solution.
Nils
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can do this with built-in Tracked Changes - there is an option to list changes on a new sheet.

Author

Commented:
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.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

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

Open in new window

Please add this at line 8:
If Intersect(Target, Range("A1:Z100")) Is Nothing Then Exit Sub

Open in new window

Author

Commented:
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

Author

Commented:
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.

Author

Commented:
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

Change this in this way?
That's right. I have used the sheet code name, you could use the actual name if that is easier.

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?
That is also correct. If you were going to do it for lots of sheets, rather than do it individually you could Workbook events or even pass an argument perhaps if it is only likely to be 3 or so probably easier to do it the way you suggest.

Author

Commented:
perfect!
thanks a lot
Nils

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial