Change shape/dimensions of cell comment in VBA 1

[ivorsa]

I generate various spreadsheets using VBA to set up formulae in cells, grids, headings etc. A s help tips I insert comments. What is the coding to set my own dimensions of the comment "boxes"

 

[combo]

A comment belongs to shapes collection, so for instance:
[tt]Worksheets(1).Shapes("Comment 1&quot.Width=200[/tt]
will change the width of comment named "Comment 1".

combo

 

[FaneDuru]

You may play with Comment boxes in this way:

'Adding, setting its name, setting its size, colouring a comment box
Sub Comm()
With Range("A6&quot
.ClearComments
.AddComment
.Comment.Text Text:="Test from Excel !"
.Comment.Visible = True
.Comment.Shape.Name = "test"
End With
ActiveSheet.Shapes("test&quot.Select
With Selection
.Width = 200
.Height = 30
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.ForeColor.SchemeColor = 10
.ShapeRange.Fill.OneColorGradient msoGradientVertical, 4, 0.3
.Font.Name = "Comic Sans MS"
.Font.Size = 15
.Font.FontStyle = "Bold"
.Font.ColorIndex = 2
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Range("A6&quot.Select
End Sub

'Autosizing and moving it
Sub Comm1()
ActiveSheet.Shapes("test&quot.Select
With Selection
.AutoSize = True
.ShapeRange.IncrementLeft -15
.ShapeRange.IncrementTop -40
End With
Range("A6&quot.Select
End Sub

'Changing the shape
Sub ChangeShape()
ActiveSheet.Shapes("test&quot.Select
If Selection.ShapeRange.AutoShapeType = msoShapeHeart Then
Selection.ShapeRange.AutoShapeType = msoShapeDiamond '4
ElseIf Selection.ShapeRange.AutoShapeType = 4 Then
Selection.ShapeRange.AutoShapeType = msoShapeCloudCallout '108
Else
Selection.ShapeRange.AutoShapeType = msoShapeHeart '21
End If
Range("A6&quot.Select
End Sub

If you put three buttons on a worksheet and assgn to each of them one of the above procedure yau may see the result.....
I hope this is what you need.
Fane Duru'