How to trigger VBA code after adding a comment?

Question

Say if you add a comment to a cell which includes the word "today", then we would like a VBA code to be tiggered to replace the "today" with today's date in that comment. But the problem here is that I could not find an event (or any other way) to know when a comment has been added, or to which cell (range object). Any ideas?

My current stupid solution is to add VBA code inside Worksheet_SelectionChange event (it's a shame that I could not get the old location before the selection change), and then do a For Each loop, check each comment in the sheet, then execute that replacement.

Answer 1

a workaround could be using a "helper" cell to store the address of the last selected cell so that once the user is done with the comment and selects another cell the event handler would check the "last" cell only

something like what follows (I used cell "A1" as "helper")

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cmt As Comment

With Target
    If .Address <> Cells(1, 1) Then
    Set cmt = Range(Cells(1, 1)).Comment
        If Not cmt Is Nothing Then
            With cmt
                .Text (Replace(.Text, "today", Date))
            End With
        End If
    End If
End With

Cells(1, 1) = Target.Address

End Sub