Adding Cell Comments with VBA

[Grasshoppa]

Hi all,

I'm currently playing with the following.............

Public comment_test() 
 
Dim unme As String 
Dim com As String 
unme = Application.UserName 
com = InputBox("comment....") 
 
ActiveCell.AddComment (unme & " " & com) 
 
End Sub 

I have noticed that if you add comments with VBA it does not place the username at the start in bold automaticaly (thats why i have added it myself) is there a quick fix for this and how do i perform a carridge return inside a coment using code?

in advance

[Andy Pope]

Hi,

If you record a macro whilst adding comments you will see that the username is added rather than appearing by default. So you have to do the same.
This will add the header text in bold and then append what extra text is specified.

Sub Test() 
    MyAddComment Range("A1"),"Hello World" 
End Sub 
 
Sub MyAddComment(ToCell As Range, Text As String) 
     
    Dim objComment As Comment 
    Dim strHeader As String 
     
    With ToCell 
        strHeader = Application.UserName & ":" & vbLf 
        Set objComment = .AddComment(strHeader & Text) 
        With objComment.Shape.TextFrame 
            .Characters.Font.Bold = False 
            .Characters(1, Len(strHeader)).Font.Bold = True 
        End With 
    End With 
     
End Sub 

[Grasshoppa]

Cheers Andy!