Friday, February 03, 2012

How to convert Excel Text to Comments using VBA

Convert Excel Range to Comments using VBA

We have seen how to Copy Comments in an Excel Sheet to a Range; now let us see how to do the opposite

Our reference Excel has Text that needs to be converted as Comments on Column E, which needs to be placed as comments

Sub Convert_Text_To_Comments()

Dim sText As String     ' Comment String
Dim i1 As Long          ' Counter
Dim sUser As String     ' User Name

sUser = Application.UserName

For i1 = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    sText = ActiveSheet.Cells(i1, 5).Value
    'Deletes Existing Comments
    Cells(i1, 3).ClearComments
    ' Creates Comment
    Cells(i1, 3).AddComment
    Cells(i1, 3).Comment.Text Text:=sUser & Chr(10) & sText
Next i1

End Sub

If you already have comments and try to AddComment then Runtime Error 1004 will be thrown. That is why it is better to Remove the existing comments (ClearComments) and proceed with Adding new comment


  1. Anonymous1:27 AM

    Thank you. It works very well. But I have in the original document, some blank rows too. And so, I do not want there to create a comment. How could I bypass this? I need, the comment to be created only if is any text in column E. If not, do not make any comment in column C.

  2. You can check for the following

    If len(trim(sText)) <> 0 then

    ... copy comments code

    End if

  3. Thanks. I would like to ask how to do something slightly different: from within a cell with a comment attached, I would like to create a macro to add the comment to the existing text within a cell in brackets like this: existing text &

  4. Anonymous11:48 AM

    Where exactly do we place this VBA Data to make it work on the spreadsheet? Is it placed within a cell on the same spreadsheet, on another sheet or elsewhere? Thanks in advance for your assistance. I'm using Excel 2010.


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.