Programming Comments in Excel 2007

Hello all,

I am looking for a code that will create a comment within the highlighted cell including the name and date/time that it was created. In addition to this I would like the code to allow another comment to be inserted by another user at a later date with the same formatting of username/date/time in the line beneath the first comment. And for this process to be repeatable.

Is it also posible to have this comment box resizeable after each comment is added, and be available for use in a shared workbook?

Many Thanks All.

Jon

You don't say how the macro is trigger or the user enters the actual comment text.

In a standard code module.

Sub AddMyComment()

    Dim objComment As Comment
    
    On Error GoTo ErrAddMyComment
    
    Set objComment = GetComment(ActiveCell)
    With objComment.Shape.TextFrame
        With .Characters
            If Len(.Text) > 0 Then
                .Text = .Text & vbNewLine
            End If
            
            .Text = .Text & Format(Now(), "dd/mmm/yyyy ") & Application.UserName & vbNewLine
            .Text = .Text & "Comment Text"
        End With
        .AutoSize = True
    End With

ErrAddMyComment:
    Exit Sub
    
End Sub

Function GetComment(MyCell As Range) As Comment

    On Error Resume Next
    
    Set GetComment = MyCell.Comment
    If GetComment Is Nothing Then
        Set GetComment = MyCell.AddComment
        GetComment.Shape.TextFrame.Characters.Text = ""
    End If
    
    Exit Function
    
End Function

You can use a input box or userform to capture users comment text.