Worksheet_Change Event only when call value changes

Question

I want the user's name and the date to be entered into a specified column when any change is made.

I also have a snippet of code that forces any data that is pasted into the sheet to be pasted as values so the sheet's formatting is maintained.

I was able to write code that functioned properly, but the event was also being triggered even when the user double clicked in a cell and clicked out of the cell (i.e., no change was made). A user could accidentally click into a cell and leave it without making changes, but their name would be left behind as having made an edit.

I tried to incorporate this solution. Here is a simplified version of my code:

Private Sub Worksheet_Change(ByVal Target as Range)

Dim DesiredRange as Range
Dim TOld, TNew as String

Set DesiredRange as 'Whatever range I'm using

If Not Intersect(Target, DesiredRange) is Nothing Then

    TNew = Target.Value

    With Application
        .EnableEvents = False
        .Undo
    End With

    TOld = Target.Value
    Target.Value = TNew

    If Application.CutCopyMode = xlCopy Then
        Application.EnableEvents = False
        Application.Undo
        Target.PasteSpecial Paste:=xlPasteValues
        Application.EnableEvents = True
    End if

    If TOld <> TNew Then
        Target.Offset(0, 23 - Target.Column) = Application.Username & vbNewLine & Date
    End If

    Application.EnableEvents = True

End if

End Sub

I am encountering the following issue:
When a user double clicks into a cell and clicks into another cell, the event is not triggered (i.e., the user's name and date is not left in the cell) but the active cell is reverted into the original cell, rather than the one they clicked into after double-clicking.
So a user would double click into a cell, do nothing, then click into another cell, and the active cell would revert to the first cell they were in.
This is also happening after the user inputs their change into the cell and presses enter.

I also encounter an error when something is pasted into the sheet, causing the code to not execute properly.

Answer 1

Prevent Worksheet Change When No Change

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ClearError
    
    Dim srg As Range: Set srg = Range("B5:E10")
    Dim irg As Range: Set irg = Intersect(srg, Target)
    
    If irg Is Nothing Then Exit Sub
        
    Dim coll As Collection: Set coll = New Collection
    
    Dim iCell As Range
    
    For Each iCell In irg.Cells
        coll.Add iCell.Value
    Next iCell
    
    With Application
        .EnableEvents = False
        .Undo
    End With
    
    Dim drg As Range
    Dim n As Long

    For Each iCell In irg.Cells
        n = n + 1
        If iCell.Value <> coll(n) Then
            iCell.Value = coll(n) ' write different value
            If drg Is Nothing Then ' combine the cells for user and date
                Set drg = iCell
            Else
                Set drg = Union(drg, iCell)
            End If
        End If
    Next iCell

    If Not drg Is Nothing Then
        ' Use 'Now' while testing or you will see no difference.
        ' Later switch to 'Date'.
        Intersect(drg.EntireRow, Columns("W")).Value = Application.UserName _
            & vbNewLine & Format(Now, "mm/dd/yyyy hh:mm:ss") ' Date
    End If

SafeExit:
    If Not Application.EnableEvents Then Application.EnableEvents = True

    Exit Sub
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume SafeExit
End Sub