View Full Version : How to wrap comment.text in vba
szcukg
09-13-2010, 10:49 AM
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
09-13-2010, 01:17 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.