Change event to Add or update comment with input box when cell in a range is changed

Hello fratello,

Here is the amended version of the macro. Let me know the results.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim oComment As Comment, cell As Range, Rng As Range
   Dim Text As String, OldText As String
   
    If Intersect(Target, Range("A:B,D:G,T:Z")) Is Nothing Then Exit Sub

    Text = InputBox("Please enter your comment below. Click OK when you are done.")
    
    For Each Rng In Target.Areas
        For Each cell In Rng

            Set oComment = cell.Comment

            If oComment Is Nothing Then
                Set oComment = cell.AddComment
                oComment.Shape.TextFrame.Characters.Text = "Entered: " & Format(Now, "mmm dd, yyyy  h:mm AM/PM") & "   " & Text & vbCrLf _
                                                         & "Value: " & Target & vbCrLf
                oComment.Shape.TextFrame.AutoSize = True
            Else
                OldText = oComment.Text
                oComment.Shape.TextFrame.Characters.Text = OldText & "Changed: " & Format(Now, "mmm dd, yyyy  h:mm AM/PM") & "   " & Text & vbCrLf _
                                                         & "Value: " & Target & vbCrLf
            End If
            
        Next cell
    Next Rng
    
End Sub

Good Morning Leith Ross
Thanks a lot for your post. This works perfectly fine, unless there are more than 5 changes to a cell. *Edit: I guess there is some kind of restriction based on the number of letters in the comment box.
Only the first 5 changes are shown. Is their a way to show the last newest 5 commentaries? Or a way to copy every comment that has been made to a seperate worksheet, so that there is a history?
Thanks a lot

Hello fratello,

The problem with the comment was it accepts only 255 characters when the string is assigned by the text property. By using the Insert method, the 255 character limit no longer applies. I made a few changes to the macro to address the problem. Let me know the results on your machine.

Private Sub Worksheet_Change(ByVal Target As Range)

' Written: August 27, 2012
' Updated: August 26, 2012 - Comment is no longer limited to 255 characters
' Author:  Leith Ross
' Summary: Tracks the changes made to a cell. A timestamp is added along with the value for each change.

   Dim oComment As Comment, Cell As Range, Rng As Range
   Dim Text As String, Newtext As String, OldText As String
   
    If Intersect(Target, Range("A:B,D:G,T:Z")) Is Nothing Then Exit Sub

    Text = InputBox("Please enter your comment below. Click OK when you are done.")
    
    For Each Rng In Target.Areas
        For Each Cell In Rng.Cells

            Set oComment = Cell.Comment

            If oComment Is Nothing Then
                Set oComment = Cell.AddComment
                oComment.Shape.TextFrame.Characters.Text = "Entered: " & Format(Now, "mmm dd, yyyy  h:mm AM/PM") & "   " _
                                                         & Text & vbCrLf & "Value: " & Target & vbCrLf
                oComment.Shape.TextFrame.AutoSize = True
            Else
                OldText = oComment.Text
                Newtext = "Changed: " & Format(Now, "mmm dd, yyyy  h:mm AM/PM") & "   " _
                        & Text & vbCrLf & "Value: " & Target & vbCrLf
                oComment.Shape.TextFrame.Characters(Len(OldText) + 1, Len(Newtext)).Insert Newtext
            End If
            
        Next Cell
    Next Rng
    
End Sub