M
MikeZz
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
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