Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify a cell | Excel Discussion (Misc queries) | |||
Cannot modify Comments | Excel Discussion (Misc queries) | |||
Modify cell contents | Excel Discussion (Misc queries) | |||
Modify Cell Sizes | Excel Worksheet Functions | |||
Modify cell from function | Excel Programming |