How do I modify Cell Comments from VBA?
Is there a way to autosize all the comment boxes in a sheet?
Hope this helps:
Sub Comments_AutoSize()
'// = = = = = = = = = = = = = = = = = = =
'// Autosize each Comment on Active Sheet
'// = = = = = = = = = = = = = = = = = = =
Dim CellComment As Comment
For Each CellComment In ActiveSheet.Comments
CellComment.Shape.TextFrame.AutoSize = True
Next CellComment
End Sub
--
Dana DeLouis
Windows XP & Office 2007
"MikeZz" wrote in message
...
I recorded a macro to see how it works and have the following code (at
end).
The problem is that I get the following error:
Invalid or Unqualified Reference - on the With .comment
I had the code working great until I determined I had to Autosize all the
comment boxes at the very end of the code because the text in each box was
of
various sizes.
The overall structure is that I create all the comments at the beginning
of
the code.
At the very end, I want to go back and modify the text and then resize it.
Is there a way to autosize all the comment boxes in a sheet?
Thanks!
MikeZz
For r = 1 To createlistqty
createFlag1 = ThisWorkbook.Worksheets("Input
Sheet").Range("createList").Offset(r, 0)
createPN = ThisWorkbook.Worksheets("Input
Sheet").Range("createList").Offset(r, 2)
currrow = ThisWorkbook.Worksheets("Input
Sheet").Range("createList").Offset(r, 2).Row
ThisWorkbook.Worksheets("Input Sheet").Range("createList").Offset(r,
-1).ClearComments
If createFlag1 < "" And createPN < "" Then
ThisWorkbook.Worksheets("Input
Sheet").Range("createList").Offset(r,
-1).Select
Selection.AddComment
Selection.Comment.Visible = False
Selection.Comment.Text Text:="Test"
With .Comment
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With
Selection.ShapeRange.TextFrame.MarginLeft = 7.2
Selection.ShapeRange.TextFrame.MarginRight = 7.2
Selection.ShapeRange.TextFrame.MarginTop = 3.6
Selection.ShapeRange.TextFrame.MarginBottom = 3.6
End If
Next r
|