How do I modify Cell Comments from VBA?
 

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

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

How do I modify Cell Comments from VBA?
 

Works like a charm!
Thanks!

"Dana DeLouis" wrote:

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