Appending an existing comment with vba

[Jockster]

The following code will add a comment to the cell to the right:

Private Sub Worksheet_Change(ByVal Target As Range)
'adds a comment box to cells in columns J, K & L when dates added to cells to the left of them
On Error Resume Next
If Not Intersect(Target, Me.Range("I6:K30")) Is Nothing Then
With Target
If .Value <> "" Then
Application.EnableEvents = False
Target.Offset(0, 1).AddComment.Text Text:="Due date: " & Target.Value + 14
On Error GoTo 0
Application.EnableEvents = True
End If
End With
End If
End sub

My question is, using code, how can I a) check if there is a comment already associated to the cell in question and then b) if there is, append that comment with "Target.Value + 28"?

Thanks

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
'   adds a comment box to cells in columns J, K & L when dates added to cells to the left of them
    Dim x As Comment
    On Error Resume Next
    If Not Intersect(Target, Me.Range("I6:K30")) Is Nothing Then
        With Target
            If .Value <> "" Then
                Application.EnableEvents = False
                With Target.Offset(0, 1)
                    Set x = .Comment
                    If x Is Nothing Then
                        .AddComment.Text Text:="Due date: " & Target.Value + 14
                    Else
                        .Comment.Text .Comment.Text & vbLf & "Due date: " & Target.Value + 14
                    End If
                End With
                On Error GoTo 0
                Application.EnableEvents = True
            End If
        End With
    End If
End Sub

 

[VoG]

Similar but with the due date formatted

Private Sub Worksheet_Change(ByVal Target As Range)
'adds a comment box to cells in columns J, K & L when dates added to cells to the left of them
If Not Intersect(Target, Me.Range("I6:K30")) Is Nothing Then
    With Target
        If .Value <> "" Then
            Application.EnableEvents = False
            With .Offset(0, 1)
                If .Comment Is Nothing Then
                    .NoteText Text:="Due date: " & Format(Target.Value + 14, "dd/mm/yy")
                Else
                    .NoteText Text:=.NoteText & vbCrLf & "Due date: " & Format(Target.Value + 14, "dd/mm/yy")
                End If
            End With
            Application.EnableEvents = True
        End If
    End With
End If
End Sub

 

[Jockster]

Thanks guys - great stuff