Track Changes without sharing

ecraig09

New Member
Joined
Jan 13, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,789
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

VBA Code:
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 ValueChange made to cellDate/Time of Change
exampleSheet2 $C$3
14/01/21 10:05​
5​
Sheet1 $E$8
14/01/21 10:05​
usefulSheet2 $A$9
14/01/21 10:05​
 

Watch MrExcel Video

Forum statistics

Threads
1,129,993
Messages
5,639,423
Members
417,089
Latest member
jonstr101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top