Excel Master Required: macro to append new text to existing cell comment

[darro]

Hi all, I have a macro that inserts a cell comment into the active cell, and I have adjusted the shape and font to my needs, but... what i want to be able to do is then select the cell with added comment and using same macro (ideally) append new text to the comment preserving the original text.

So ctrl K inserts new comment if no comment exists but ctrl K also opens the comment in edit mode if it already exists. Do you see?

The macro as I have it is below, any help would be much appreciated.
I await your excel mastery!

Sub AddNewCom()
     
    Dim strCommentName As String
    Dim cmnt As String
    Dim Today
    Today = Now
    cmnt = InputBox("Please enter a comment")
    strCommentName = "User:    " & cmnt & vbLf & Now
    On Error GoTo 0

  
    If Not activeCell.Comment Is Nothing Then GoTo 0
     
    With activeCell.AddComment(strCommentName)
        .Visible = False
        .Shape.AutoShapeType = msoShapeRoundedRectangle
        With .Shape.TextFrame
            With .Characters(0, Len("Greer:")).Font
                .Bold = True
                .Italic = True
                .ColorIndex = 3
            End With
        End With
    End With
    With activeCell
        .Comment.Shape.TextFrame.AutoSize = True
        .Comment.Visible = False
    End With
    
    Exit Sub
     
0:
    
    If Err.Number <> 0 Then MsgBox Err.Description
     
End Sub

 

[xld]

Show what you have.

 

[darro]

so I have this:

Sub AddCom()
Dim strCommentName As String
Dim cmnt As String
    
    cmnt = InputBox("Please enter a comment")
    strCommentName = cmnt & vbLf & Now & vbLf
    On Error GoTo 0

    With activeCell
    
        If .Comment Is Nothing Then
        
            strCommentName = "Greer:" & Chr(10) & strCommentName
        Else
    
            strCommentName = .Comment.Text & "Greer:" & Chr(10) & Chr(10) & strCommentName
            .Comment.Delete
        End If
     
        With .AddComment(strCommentName)
            .Visible = False
            .Shape.AutoShapeType = msoShapeRoundedRectangle
            With .Shape.TextFrame
                With .Characters(0, Len("Greer:")).Font
                    .Bold = True
                    .Italic = True
                    .ColorIndex = 3
                End With
            End With
        End With
        
        .Comment.Shape.TextFrame.AutoSize = True
        .Comment.Visible = False
    End With
     
End Sub

How can I make the text "Greer:" always appear bold, italic & red... or any other formatting that I like?

 

[xld]

Sub AddCom()
Const USERNAME As String = "Greer:"
Dim strCommentName As String
Dim cmnt As String
Dim NoMore As Boolean
Dim Pos As Long
    
    cmnt = InputBox("Please enter a comment")
    strCommentName = cmnt & vbLf & Now & vbLf
    On Error GoTo 0

    With ActiveCell
    
        If .Comment Is Nothing Then
        
            strCommentName = USERNAME & Chr(10) & strCommentName
        Else
    
            strCommentName = .Comment.Text & Chr(10) & USERNAME & Chr(10) & strCommentName
            .Comment.Delete
        End If
     
        With .AddComment(strCommentName)
        
            .Visible = False
            .Shape.AutoShapeType = msoShapeRoundedRectangle
            
            Pos = 0
            Do
            
                Pos = InStr(Pos + 1, strCommentName, USERNAME)
                If Pos > 0 Then
                
                    With .Shape.TextFrame
                    
                        With .Characters(Pos, Len(USERNAME)).Font
                        
                            .Bold = True
                            .Italic = True
                            .ColorIndex = 3
                        End With
                    End With
                End If
            Loop Until Pos = 0
        End With
        
        .Comment.Shape.TextFrame.AutoSize = True
        .Comment.Visible = False
    End With
     
End Sub

 

[darro]

Thats spot on!! Cheers Maestro!

 

[darro]

OK, further developments. I have the code above on a mac and it performs perfectly, I also have it on a windows pc and it doesn't autosize the comment box. Unless I return the comment box basically just keep going sideways. Also is there a character limit for a cell comment box and if so, what is it?

Need the help of an excel master again!

 

[darro]

How can i dictate the length of text before the text is wrapped? Can I keep the vertical autosize and also define the width of the comment box?

 

[darro]

or can I limit the length of a string before it returns to a new line? how would I do that in this code?

 

[mikerickson]

I responded in the other thread that perhaps you should try replacing Chr(10) with Chr(13).