Results 1 to 4 of 4

Appending an existing comment with vba

This is a discussion on Appending an existing comment with vba within the Excel Questions forums, part of the Question Forums category; The following code will add a comment to the cell to the right: Private Sub Worksheet_Change(ByVal Target As Range) 'adds ...

  1. #1
    Board Regular
    Join Date
    Jan 2009
    Location
    Isle of Man
    Posts
    57

    Question Appending an existing comment with vba

    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,516

    Default Re: Appending an existing comment with vba

    Try:

    Code:
    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

  3. #3
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,512

    Default Re: Appending an existing comment with vba

    Similar but with the due date formatted

    Code:
    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

  4. #4
    Board Regular
    Join Date
    Jan 2009
    Location
    Isle of Man
    Posts
    57

    Thumbs up Re: Appending an existing comment with vba

    Thanks guys - great stuff

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com