How to wrap comment.text in vba

[szcukg]

I have a situation in which text in one of the cells is massive.When I change the contents of this cell the previous value becomes a comment to that cell.Now the problem is, this text is so big that I cannot see complete text.If I use .Shape.Textframe.Autosize=true then I have to go on browsing till god knows when to see the text.
What I need to do is whatever and however big the text might be in Commnet.text,I want to show it in one and one place only.i.e when I hover on comment.

[NomalAnomaly]

I didn't see an option to wrap a comment's text however, there is an option to resize the comment to a desired size.

The following VBA code will resize all the comments in a sheet to a specific size:


Sub ResizeComments()

Dim C As Comment

For Each C In ActiveSheet.Comments
With C
.Shape.Width = 500
.Shape.Height = 500
End With
Next

End Sub



If you don't want all comments to be resized then you can add a condition such as to resize a comment to a specific size based on the amount of text. Then you would do something like:




Sub ResizeCommentsByLength()

Dim C As Comment

For Each C In ActiveSheet.Comments
With C
If Len(.Text) > 100 Then
.Shape.Width = 500
.Shape.Height = 500
End If
End With
Next

End Sub



Hope that helps...
NormalAnomaly