Is there a way to track the changes that take place in a shared workbook using a macro?

Question

I have a shared master sheet which is accessed by multiple users.I want to track the changes made by the users using a macro and then keep the change made by the first user and give other users a msgbox saying change already made.Now there is an option available in the "share workbook" option in the ribbon which says "Ask me which changes win" but i want the first user to make the change without any dialogue box pop up. I tried the "Changes being saved win" option however it doesn't work as intended.Is there a way to do it using a macro? If yes,how?

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)

Set FindCell = Worksheets("Track Changes").Columns(2).Find(Target.Address, LookIn:=xlValues)
R = Worksheets("Track Changes").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

If FindCell Is Nothing Then
    With Worksheets("Track Changes")
        .Cells(R, 1).Value = Date
        .Cells(R, 2).Value = Target.Address
        .Cells(R, 3).Value = Application.UserName
        If Target.Value = "" Then
             .Cells(R, 4).Value = "Empty cell"
        Else
             .Cells(R, 4).Value = Target.Value
        End If
    End With
Else
    firstAddress = FindCell.Address
    Do
        If FindCell.Offset(0, -1).Value = Date Then
            MsgBox "Changes already made by " & FindCell.Offset(0, 1).Value & _
                vbNewLine & "Changes: " & FindCell.Offset(0, 2).Value
            Exit Sub
        End If

        Set FindCell = Worksheets("Track Changes").Columns(2).FindNext(FindCell)
    Loop While Not FindCell Is Nothing And FindCell.Address <> firstAddress

    With Worksheets("Track Changes")
        .Cells(R, 1).Value = Date
        .Cells(R, 2).Value = Target.Address
        .Cells(R, 3).Value = Application.UserName
        If Target.Value = "" Then
             .Cells(R, 4).Value = "Empty cell"
        Else
             .Cells(R, 4).Value = Target.Value
        End If
    End With
End If

End Sub