MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Extract text from Comments


Posted by Simon James-Morse on August 16, 2001 2:13 AM

Is there a function/technique to extract the text placed in cell Comments?

Simon


Posted by Dax on August 16, 2001 2:24 AM

Do you want the comment text just extracted from a particular cell or all cells?

This macro will extract all comments from the active sheet and display them in a message box.

Sub GetCommentText()
Dim cm As Comment
Dim sht As Worksheet

Set sht = ActiveSheet
For Each cm In sht.Comments
MsgBox cm.Text
Next
End Sub

Regards,
Dax

Posted by Simon James-Morse on August 16, 2001 2:33 AM

This is a good start! (Thx Dax)

I want to extract the Comment text of one cell and place it in another (adjoining?) cell as 'normal' text.

Simon

Posted by Dax on August 16, 2001 4:17 AM

Simon,

How about this?

Dax.

Sub PutCommentTextInCell()
Dim cm As Comment
Dim rngeCommentSource As Range
Dim rngeDestination As Range
Dim sCommentText As String

Set rngeCommentSource = Range("D8") 'Change this to what you want
Set rngeDestination = Range("A1") 'Ditto

sCommentText = rngeCommentSource.Comment.Text
rngeDestination.Value = sCommentText
End Sub