Announcement

Collapse
No announcement yet.

Adding Cell Comments with VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Adding Cell Comments with VBA

    Hi all,

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

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

  • #2
    Re: Adding Cell Comments with VBA

    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

    Cheers
    Andy

    Comment


    • #3
      Re: Adding Cell Comments with VBA

      Cheers Andy!

      Comment

      Working...
      X