Selected Answer
Yes. You're right. This isn't a big thing. Please install the code below in the code module of the worksheet on which you want the action. There can be several such installations in the same workbook which would work independent from each other.
Private Sub Worksheet_Change(ByVal Target As Range)
' 009
Const TriggerClm As String = "D" ' change to suit
Dim TriggerRng As Range ' range in which to set comments
Dim Cell As Range ' loop object
Dim Cmt As Comment
Dim Txt As String ' comment text
Dim Leng(1) As Integer ' length of UserName / Txt
With Target
' don't process large paste or delete jobs
' change the maximum to suit
If .Cells.CountLarge > 1000 Then Exit Sub
' TriggerRng is set to start from row 2 to
' one row below the last used row. Change first row to suit.
Set TriggerRng = Range(Cells(2, TriggerClm), _
Cells(Rows.Count, TriggerClm).End(xlUp).Offset(1))
If Not Application.Intersect(TriggerRng, Target) Is Nothing Then
For Each Cell In Target
' create a text like John Smith
' 19-05-20 16:43
Txt = Application.UserName & vbCrLf
Leng(0) = Len(Txt)
' change the date/time format to suit
Txt = Txt & Format(Now(), "dd-mm-yy hh:mm")
Leng(1) = Len(Txt)
If .Column = Columns(TriggerClm).Column Then
Set Cmt = Cell.Comment
If Cmt Is Nothing Then
Set Cmt = Cell.AddComment(Txt)
Else
' insert new text before existing
Cmt.Text Txt & vbCrLf, , False
End If
End If
With Cmt.Shape.TextFrame
.Characters(1, Leng(0) - 2).Font.Bold = True
.Characters(Leng(0) + 1, Leng(1) - Leng(0)).Font.Bold = False
End With
Next Cell
End If
End With
End Sub
The minimum required adjustment to the code is to set the trigger column, that is the column where you want comments added. It's the Const TriggerClm value you have to set. For more adjustments I recommend that you read the comments in spread throughout the code. Almost everything is customizable, often without more than a passing knowledge of code. But here is one piece you may not find easily on your own. This line of code determines that any pre-existing comment should be kept. It also inserts a Carriage return / Line feed between the old and new comments.
Cmt.Text Txt & vbCrLf, , False
If you want to replace the old comment with the new one this line should read like this.
Cmt.Text Txt
