Help Required to Track / Report user changes in a worksheet

[anishms]

Hi,

Request expert's help in coding to track user changes in one worksheet to another worksheet.
In the workbook attached, I need to track all the changes made in sheet 'Follow-up Tracker'. And the changes shall be recorded in sheet "Audit Trail" like the samples given.
Changes shall include pasting new data in the sheet 'Follow-up Tracker'.
Sheet "Audit Trail" shall be password protected to avoid any manual changes by the users.

Thanks in Advance for your Help

 

[vletm]

anishms
Have You checked this...?

 

[anishms]

anishms
Have You checked this...?
View attachment 71777

Thanks a lot, I checked this and meeting 95% of the requirement

 

[Logit]

.
Here is a macro that will track changes. Create a sheet in your workbook named TRACKER. This macro goes in the
ThisWorkbook Module :

Option Explicit

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim bBold As Boolean

    With Application
         .ScreenUpdating = False
         .EnableEvents = False

    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
            
        With Sheets("Tracker")
                
            If .Range("A1") = vbNullString Then
                .Range("A1:F1") = Array("Cell Changed", "Old Value", _
                    "New Value", "Time of Change", "Date of Change", "User")
            End If
                
            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name & " : " & Target.Address
                  .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              
            If bBold = True Then
                .ClearComments
                .AddComment.Text Text:= _
                     "NOTE :" & Chr(10) & "" & Chr(10) & _
                        "Bold values are the results of formulas"
            End If
                .Value = Target
                .Font.Bold = bBold
                
            End With
                .Offset(0, 3) = Time
                .Offset(0, 4) = Date
                .Offset(0, 5) = Application.UserName
            End With
            
            Sheet2.Range("A1:F1").Font.Bold = True
            .Cells.Columns.AutoFit
        
        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With

On Error GoTo 0

MsgBox "There was a change to this sheet !"  ' Delete this to prevent User knowledge.

End Sub

'This sub auto displays the TRACKER sheet after each change.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target
End Sub

 

[anishms]

Thanks! tested and working
But the tracker is referring to changes in cell address, if a new row or column is added in between then the changes recorded in the tracker till that time goes for a toss.

 

[Logit]

Here are two images where I used the submitted macro from above :

Copied & Inserted Row (copied from row 9 and inserted into row 8) :



Tracker Log showing a new row had been inserted in Row #8 :



The Tracker Log is indeed capturing an inserted row.

 

[anishms]

Here are two images where I used the submitted macro from above :

Copied & Inserted Row (copied from row 9 and inserted into row 8) :

View attachment 71844

Tracker Log showing a new row had been inserted in Row #8 :

View attachment 71845

The Tracker Log is indeed capturing an inserted row.

h

Here are two images where I used the submitted macro from above :

Copied & Inserted Row (copied from row 9 and inserted into row 8) :

View attachment 71844

Tracker Log showing a new row had been inserted in Row #8 :

View attachment 71845

The Tracker Log is indeed capturing an inserted row.

What if you insert a new blank row in before 8th row. The data cell becomes G9 and G10 whereas the tracker will still show G8 and G9. Relative referencing is not working

 

[Logit]

What if you insert a new blank row in before 8th row. The data cell becomes G9 and G10 whereas the tracker will still show G8 and G9. Relative referencing is not working

And that would be accurate, because you are inserting a new row to replace the existing. What you are saying is not incorrect and it does not change the accuracy of the Tracker.

 

[Logit]

I suspect you are meaning something different from what you are saying. ???

You'll need to word it differently.