Worksheet_Change when copy/cut/paste

  • Hello,

    I have code that generates a record/comment every time a cell is altered, but it does not do the same for multiple cells when they are being pasted into. How do I get this code to add a comment for any cell that is altered whether individually or in a multi-cell paste function?

    Private Sub Worksheet_Change(ByVal Target As Range)
    Const xRg As String = "A1:Z1000" 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 ActiveSheet.Unprotect Password:="test" Application.EnableEvents = False strOld = .Text .Value = strNew Application.EnableEvents = True strCmt = "Edit: " & Format$(Now, "dd Mmm YYYY hh:nn: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 ActiveSheet.Protect Password:="test"
    End Sub

  • Hello,

    My assumption is that you meant :

    Thanks for confirming ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!