Track changes within a workbook without sharing the workbook

Looking to track changes on a separate tab in a workbook without placing the workbook as a "Shared Workbook". I have heard the best way would be to use a VBA Event Handler. I am not good with code, I could understand it by reverse engineering it if I had some idea of what the code meant in relation to the tabs.
Thanks

.
Paste this code in the ThisWorkbook module :

Dim vOldVal 'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim strUserName As String
Dim xFormula As Boolean
Dim xDate As Boolean
Dim xHead As Range
Dim xTitle As Range

Set xHead = Sheets("Track_Changes").Range("B3:H3")
strUserName = Application.UserName

On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
    
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
    
    If IsEmpty(vOldVal) Then vOldVal = "[empty cell]"
    xFormula = Target.HasFormula
    xDate = IsDate(Target)

    With Sheets("Track_Changes")
        .Unprotect Password:="Password"
       
                If .Range("B2") = vbNullString Then
                    xHead = Array("DATE OF CHANGE", "TIME OF CHANGE", "SHEET NAME", "CELL CHANGED", "CHANGE BY", "OLD VALUE", "NEW VALUE")
                Sheets("Track_Changes").Columns(1).ColumnWidth = 3
                
                .Range("B1").Value = "Track Changes"
                .Range("B1").Font.Size = 18
                                
                With xHead
                    .Interior.Color = RGB(30, 139, 195)
                    .Font.Color = vbWhite
                    .Font.Bold = True
                End With

                With xHead.Borders(xlInsideVertical)
                    .Color = vbWhite
                    .Weight = xlMedium
                End With
                End If
            
        
        With .Cells(.Rows.Count, 2).End(xlUp)(2, 1)
                    .Borders(xlInsideVertical).Color = RGB(255, 191, 191)
                    .Borders(xlInsideVertical).Weight = xlMedium
                    
                    .Value = Date
                    .Offset(0, 1) = Format(Now, "hh:mm:ss")
                    .Offset(0, 2) = Target.Parent.Name
                    .Offset(0, 3) = Target.Address
                    .Offset(0, 4) = strUserName
                    .Offset(0, 5) = vOldVal
                  
                    With .Offset(0, 6)
                        If xFormula = True Then
                            .ClearComments
                            .AddComment.Text Text:="Cell is bold as value contains a formula"
                        End If
                        If xDate = True Then
                            .NumberFormat = "dd/mm/yyyy"
                        End If
                        .Value = Target
                        .Font.Bold = xFormula
                        If IsEmpty(Target) Then .Value = "[empty cell]"
                    End With
                    
            End With

            .Cells.Columns.AutoFit
            .Cells.Columns.HorizontalAlignment = xlLeft
            
n = Sheets("Track_Changes").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count - 1
With Sheets("Track_Changes").Range("B4:H" & n + 2)
        .Borders(xlInsideHorizontal).Color = RGB(30, 139, 195)
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Borders(xlInsideVertical).Color = RGB(200, 200, 200)
        .Borders(xlInsideVertical).Weight = xlThin
End With
.Protect Password:="Password"
        End With
    vOldVal = vbNullString

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

On Error GoTo 0

End Sub



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

On Error Resume Next
If Selection.Cells.Count > 1 Then Exit Sub 'Avoid runtime error 7
    vOldVal = Target

End Sub

You can hide the TRACKER sheet, which is password protected, so only you have access.

Hi Logit,
Thanks for your help. I did not see your response until today when I came back to work. I will attempt this but do you have the password to the protected sheet. I attempted to view the code on this sheet and cannot see it. Is that because it is protected?

Thanks again.

Hi Logit, Do you know a way to add computer name or host name?

Thanks,

Madison

.
I added the 'Environ' command here :

.Value = Date
                    .Offset(0, 1) = Format(Now, "hh:mm:ss")
                    .Offset(0, 2) = Target.Parent.Name
                    .Offset(0, 3) = Target.Address
                    .Offset(0, 4) = strUserName
                    .Offset(0, 5) = vOldVal
                    .Offset(0, 7) = Environ("username") 

Thanks Logit. I changed it to "ComputerName". This is the best tracking code I have ever seen.

.
You are welcome.

I agree ... it's a pretty nifty project. Can't claim credit for most of it though ... got most of it from somewhere.

Cheers.

ps: Be certain to change the wording in the Macro as well for "ComputerName"

If .Range("B2") = vbNullString Then
                    xHead = Array("DATE OF CHANGE", "TIME OF CHANGE", "SHEET NAME", "CELL CHANGED", "CHANGE BY", "OLD VALUE", "NEW VALUE", "COMPUTERNAME")
                Sheets("Track_Changes").Columns(1).ColumnWidth = 3

.
The code is on the ThisWorkbook module. Once you get into the VBE, doubleclick on ThisWorkbook. The project does not have a password.

Logit,
I wanted to say thank you for your help on this. It is working great, there are few things that it will not track but at this time it is working and I am happy with the results. It auto saves after every entry and will not let you undo it.

Thank you for your time.

.
What is it not tracking ? I haven't noticed that here. Curious.

Does not track when you autofill down.(Number Sequence)
Does not track when you insert or delete rows or columns.
Does not track merged cells old value.
Does not track copy & paste to multiple cells, only one at a time.
Sometimes you may have double click the cell then press enter for it to acknowledge the change.
Hope this helps.

Hello @logit, I was trying to use your file - Track Changes Log - xlsx sheet.xlsm‎ and I want to delete the data from the Track changes sheet.. But while trying to delete its asking for a password. Is it password protected?

.
No passwords. I don't understand why that is happening for others.

Here is the file again. Hopefully it won't cause any issues :

Hi

Thanks for uploading.

Unprotect Password:="Password"

found the password

Thanks for the code, so helpful to begin with solving my problem.

Hi
Thanks for sharing this! It was perfect for what I'm working on.

Is there a way to have it not track changes for certain sheets?

Hi,
Great code! But if I want it to show the old value on merged cells, how do I do this?