Another question about comment

[Excelboo2]

I have written the following scripts:

For I = 1 to 10
Cells(I,1).AddComment
Cells(I, 1).Comment.Text Text:="ABC "
Cells(I, 1).Comment.ShapeRange.ScaleHeight 0.33, msoFalse, msoScaleFromTopLeft

Next I

I want to change the size of the comment box and the size of text inside the comment box but it doesn’t work. Please help. Many Thanks

 

[Gary McMaster]

The comment is considered a shape. More specifically a rectangle. If you have "Rectangles" in your worksheet that are not comments, the code below will alter those too.

You may have to test additional properties to single out only "Comment Rectangles". Testing the fill, or background, color is the first thing that comes to mind.

Hope it helps.

Gary

Dim oShape As Shape

For Each oShape In ActiveSheet.Shapes
    If oShape.AutoShapeType = msoShapeRectangle Then
        oShape.Width = 200
        oShape.Height = 100
        oShape.TextFrame.Characters.Font.Size = 16
    End If
Next