Code to Track Changes In Excel

[sspatriots]

Hi,

I'm trying to use some code that I found in the YouTube video below that allows you to easily track changes to a range in a worksheet. The link is below:

How to Track Changes In Excel Without Sharing The Workbook

The code will be shown below. The issue I'm having is that it will not create a change comment for a cell that references a drop-down list from another worksheet. When I try and select a cell that references a drop-down list from another worksheet, it gives me the error message: "Run-time error '1004': Method 'Undo' of object '_Application' failed". When I select the "Debug" option it takes me to the line in the code that says:

Application.Undo

I'm hoping someone can help me with a quick fix to the existing code that will make this work without disrupting the drop-down list that are used for each cell because we need to change them often between the following statuses:

Paid

NA

PRTL PMT

Invoiced

REQD

Hold

Private Sub Worksheet_Change(ByVal Target As Range)

  'Subscribe to youtube.com/excel10tutorial

'    Const xRg As String = "A1:Z1000"
    Const xRg As String = "P1:R1002"

    Dim strOld As String

    Dim strNew As String

    Dim strCmt As String

    Dim xLen As Long

    With Target(1)

        If Intersect(.Cells, Range(xRg)) Is Nothing Then Exit Sub

        strNew = .Text

        Application.EnableEvents = False

        Application.Undo

        strOld = .Text

        .Value = strNew

        Application.EnableEvents = True

        strCmt = "Edit: " & Format$(Now, "dd Mmm YYYY hh:mm:ss") & " by " & _
        Application.UserName & Chr(10) & "Previous Text :- " & strOld

        If Target(1).Comment Is Nothing Then

            .AddComment

        Else

            xLen = Len(.Comment.Shape.TextFrame.Characters.Text)

        End If

        With .Comment.Shape.TextFrame

            .AutoSize = True

            .Characters(Start:=xLen + 1).Insert IIf(xLen, vbLf, "") & strCmt

        End With

    End With

End Sub

Any help would be greatly appreciated. Thanks, SS

 

[sspatriots]

I did not mention that all of the drop-down list that have the problem are in a table. Therefore, the issue could be that the code doesn't cooperate with a table being used. I would just like to find a way to make this work. Thanks, SS

 

[sspatriots]

FYI, I have moved on from this original post with a different solution I'm using from another post of mine that was resolved today. If anyone feels like tackling this, I'd love to see how this one turns out, but not paramount for me right now.

Alternate Solution: Workbook_SheetSelectionChange event