Track changes in excel without sharing

Question

Hi

I want to track the changes done by members without sharing workbook.

Is there any VBA to perform the task. Kindly help me.

My data context is from B5:O254 and existing VBA exist to perform other task.

Kindly have a look over this code and append extra codes here itself.

Note: Using Office 2016 For Mac

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim inputRange As Range

    'tell this sub to unprotect only these cells

    Set inputRange = Me.Range("I5:I255")

    ' If the selected cell is not in the range keep the sheet locked

    If Intersect(Target, inputRange) Is Nothing Then

    'else unprotect the sheet by providing password

    '(same as the one that was used to protect this sheet)

    Else

    Me.Unprotect Password:="GOOD"

    Target.Locked = False

    'Me.Protect Password:="GOOD", Userinterfaceonly:=True, AllowFiltering:=True

    End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim cel As Range

    If Not Intersect(Me.Range("I5:I255"), Target) Is Nothing Then

    Application.ScreenUpdating = False

    Application.EnableEvents = False

    Me.Unprotect Password:="GOOD"

    For Each cel In Intersect(Me.Range("I5:I255"), Target)

    If cel.Value = "" Then

    'Cel.Offset(0, 2).ClearContents

    Else

    If cel.Offset(0, 2) = "" Then

    With cel.Offset(0, 2)

    .NumberFormat = "hh:mm:ss"

    .Value = Time

    End With

    End If

    End If

    Next cel

    Application.EnableEvents = True

    Application.ScreenUpdating = True

    'Me.Protect Password:="GOOD", Userinterfaceonly:=True, AllowFiltering:=True

    End If

    Dim xRg As Range, xCell As Range

    On Error Resume Next

    If (Target.count = 1) Then

        If (Not Application.Intersect(Target, Me.Range("I5:I255")) Is Nothing) Then _

            Target.Offset(0, -6) = Date

        Application.EnableEvents = False

        Set xRg = Application.Intersect(Target.Dependents, Me.Range("I5:I255"))

        If (Not xRg Is Nothing) Then

            For Each xCell In xRg

                xCell.Offset(0, -6) = Date

            Next

        End If

        Application.EnableEvents = True

        Application.ScreenUpdating = True

        'Me.Protect Password:="GOOD", Userinterfaceonly:=True, AllowFiltering:=True

    End If

End Sub

Thanks in advance....

Answer 1

As noted in this Support article, "Change tracking is available only in shared workbooks."

Track changes in a Shared Workbook - Office Support

Answer 2

Hi

Yes your understanding is some how correct but I want to track changes in same workbook.

Let me explain you briefly:

Suppose one member enter data on Column I then an offset of 2 column Time stamp occurs but I want if member delete the data from Column I and overwrite another data then that time and data also get captured in another sheet or same sheet.

Thanks in advance...

Answer 3

Hi

Yes your understanding is some how correct but I want to track changes in same workbook.

Let me explain you briefly:

Suppose one member enter data on Column I then an offset of 2 column Time stamp occurs but I want if member delete the data from Column I and overwrite another data then that time and data also get captured in another sheet or same sheet.

Thanks in advance...

Answer 4

If I have understood your query correctly (pls correct me if I am wrong in my understanding), you want to track changes in some workbook but do not wish to share the tracking information captured.

To track changes without sharing a workbook means that the tracking has to be done in some other workbook regarding the changes made in another workbook.

I am not sure if Excel VBA has that capability of tracking changes of another workbook.