Track Changes without sharing

[ecraig09]

Hi there,

My coworker and I are in need of either a code or a macro to help us out. Our staff track their work on an excel sheet currently with various tabs and functions. They turn the documents in weekly, and we are trying to see if there is a way that we can see when changes were made to the document when they turn it in to us. Ideally we would like this to be on a hidden additional tab. While the track changes History tab gives us a lot of what we are looking for, unfortunately we cannot have these be shared documents for privacy reasons. But the same basic idea is what we are looking for. An additional tab -- that we can hide and unhide -- that will log the date and time of any changes that they make to the document.

Any help is appreciated!

 

[mrshl9898]

Adding this code to a worksheet module will add values to a sheet added called "tracker". I added it to 2 sheets and made some changes.

Range("A1:Z" & LastRowS) will look at any changes made in A:Z

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim tracker As Worksheet
    Dim mysheet As Worksheet
    Dim LastRowT As Long
    Dim LastRowS As Long

    Set tracker = Sheets("tracker")
    Set mysheet = ActiveSheet
    LastRowT = tracker.Cells(tracker.Rows.Count, "A").End(xlUp).Row + 1
    LastRowS = mysheet.UsedRange.Rows(mysheet.UsedRange.Rows.Count).Row

    Set KeyCells = Range("A1:Z" & LastRowS)

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        Range(Target.Address).Copy tracker.Cells(LastRowT, 1)
        tracker.Cells(LastRowT, 3) = Now()
        tracker.Cells(LastRowT, 2) = mysheet.Name & " " & Target.Address
    End If
End Sub

Updated Value	Change made to cell	Date/Time of Change
example	Sheet2 $C$3	14/01/21 10:05​
5​	Sheet1 $E$8	14/01/21 10:05​
useful	Sheet2 $A$9	14/01/21 10:05​