VB ; How do I detect a Comment in a cell

[rdwj]

I have a table where some cells have comments attached ("Insert" -
"Comments"). As these comments are poorly visible, I am trying to lift the
text in a comment box such that I can make it more visible - I want to show
the text that is included in the comment of the selected cell in a designated
worksheet cell (in this case the top left cell of the table)

I can read the text in the comment through

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A2") = Mid(Target.Comment.Shape.AlternativeText, 10)
end sub

(I have included Mid(...,10) to get rid of the "TextBox : ")

However - if a cell does NOT have a comment, the above gives an error.
How can I ensure that does not block the rest of the code from running (ie
more code under Worksheet_SelectionChange)

Txs,

RDWJ

 

[Kevin B]

You can use the following macros to show/hide all comments in the currently
acitve worksheet:

Sub DisplayAllComments()

Dim c As Comment
Dim i As Integer
Dim strNoComments As String

strNoComments = "There were no comments found in the " & _
ActiveSheet.Name & " worksheet."

For Each c In ActiveSheet.Comments
i = i + 1
c.Visible = True
Next c

If i = 0 Then MsgBox strNoComments

Set c = Nothing
Exit Sub

End Sub

Sub HideAllComments()

Dim c As Comment

For Each c In ActiveSheet.Comments
c.Visible = False
Next c

End Sub

 

[rdwj]

Kevin,
I do not want to show or hide comments - I want to take the text out of the
comment and place it in a cell.....
I could use your basis to determine cell address of every comment, and based
on that determine if the target cell has a comment, however, that takes a lot
of calculating time.....
Thanks for trying.
Rob

 

[Kevin B]

The following macro posts the comments found, starting in A1 of the currently
active worksheet. The integer variable i increments the offset value so that
each subsequent comment is posted one cell below the other.

Sub PostAllComments()

Dim c As Comment
Dim i As Integer
Dim r As Range

Set r = ActiveSheet.Range("A1")

For Each c In ActiveSheet.Comments
r.Offset(i).Value = c.Text
i = i + 1
Next c

Set c = Nothing
Set r = nothing
Exit Sub

End Sub

 

[Kevin B]

I neglected to say that the starting cell is cell A1 of the current workbook.
You can change this to whatever cell you want to start the list of comment
text in.

 

[Gord Dibben]

Have a look at Debra Dalgleish's site for some Dave Peterson code which places
comment text in an adjacent cell.

http://www.contextures.com/xlcomments03.html#CopyAdjacent


Gord Dibben MS Excel MVP