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

4 comments:

  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.

    ReplyDelete
  2. You can check for the following

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

    ... copy comments code

    End if

    ReplyDelete
  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 &

    ReplyDelete
  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.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group