Autoshape type

[tpmccarthy]

I'm trying to make a Comment box with trapezoidal shape. I don't know how to describe/call a shape in the code.Is there a list of shape names to use in a macro?
Any help is appreciated. Thanks

 

[JoeMo]

Shapes are named by Excel when they are created. Excel uses default names like Trapezoid 1, Trapezoid 2, ...., Oval 1, .... etc.
Here's a bit of code that allows you to identify all the shapes on the active sheet, and in the case of a trapezoid, it renames it to "MyTrap N". where N is the Nth trapezoid on the sheet.

You can rename any shape by selecting it and then overwriting its name in the box just above the col A header and pressing Enter.

Sub GetShapeName()
Dim Shp As Shape, Ct As Long
With ActiveSheet
    For Each Shp In .Shapes
        Shp.Select
        MsgBox Shp.Name
        'change the shape name if desired
        If Shp.Name Like "Trapezoid*" Then
            Ct = Ct + 1
            Shp.Name = "MyTrap" & " " & Ct
        End If
    Next Shp
End With
End Sub

 

[Yongle]

welcome to MrExcel

something like this?

Sub AddTrapezoid()
    Dim shp As Shape, ws As Worksheet
    Set ws = ActiveSheet
    Set shp = ws.Shapes.AddShape(msoShapeTrapezoid, 10, 10, 100, 20)
    shp.Fill.ForeColor.RGB = RGB(0, 0, 255)
    shp.TextFrame.Characters.Text = "yongle wrote this"
    shp.TextFrame.Characters.Font.ColorIndex = 2
    shp.Name = "my comment box"
End Sub

 

[tpmccarthy]

Shapes are named by Excel when they are created. Excel uses default names like Trapezoid 1, Trapezoid 2, ...., Oval 1, .... etc.
Here's a bit of code that allows you to identify all the shapes on the active sheet, and in the case of a trapezoid, it renames it to "MyTrap N". where N is the Nth trapezoid on the sheet.

You can rename any shape by selecting it and then overwriting its name in the box just above the col A header and pressing Enter.

Sub GetShapeName()
Dim Shp As Shape, Ct As Long
With ActiveSheet
    For Each Shp In .Shapes
       *[B]Shp.Select[/B]
        MsgBox Shp.Name
        'change the shape name if desired
        If Shp.Name Like "Trapezoid*" Then
            Ct = Ct + 1
            Shp.Name = "MyTrap" & " " & Ct
        End If
    Next Shp
End With
End Sub

Thanks JoeMo,
But I can't get the code to run past Shp.Select. it's likely something I'm doing/not doing. I've been "out of the game" (retired) for a number years and I'm stunned by how much I've forgotten. Little help?

 

[tpmccarthy]

welcome to MrExcel

something like this?

Sub AddTrapezoid()
    Dim shp As Shape, ws As Worksheet
    Set ws = ActiveSheet
    Set shp = ws.Shapes.AddShape(msoShapeTrapezoid, 10, 10, 100, 20)
    shp.Fill.ForeColor.RGB = RGB(0, 0, 255)
    shp.TextFrame.Characters.Text = "yongle wrote this"
    shp.TextFrame.Characters.Font.ColorIndex = 2
    shp.Name = "my comment box"
End Sub

Thanks Yongle
I get you code to run "as is" I can't figure out how to incorporate it into the code I have. (snippet below)
Appreciate any guidance
***********************************
Sub formatComments()

Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.AutoShapeType = msoShapeRoundedRectangle

 

[Yongle]

Try this instead

I have added a few other things so that you can see how to alter various properties

Sub CommentShape()
    Dim MyComments As Comment
    
    For Each MyComments In ActiveSheet.Comments
        With MyComments
            .Shape.AutoShapeType = msoShapeTrapezoid
            .Shape.TextFrame.Characters.Font.Name = "Tahoma"
            .Shape.TextFrame.Characters.Font.Size = 10
            .Shape.TextFrame.Characters.Font.ColorIndex = 2
            .Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
            .Shape.Line.BackColor.RGB = RGB(255, 255, 255)
            .Shape.Fill.Visible = msoTrue
            .Shape.Fill.ForeColor.RGB = RGB(25, 25, 150)
            .Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
        End With
    Next MyComments
End Sub

 

[tpmccarthy]

Thanks Yongle
This'll be a good tutorial for me . Plus helps with my immediate issue.
I've been retired for quite a while and I'm shocked at what I've forgotten,or never really knew.
Thanks again

 

[JoeMo]

Thanks JoeMo,
But I can't get the code to run past Shp.Select. it's likely something I'm doing/not doing. I've been "out of the game" (retired) for a number years and I'm stunned by how much I've forgotten. Little help?

Perhaps you are running the code when the active sheet has no shapes on it??

 

[tpmccarthy]

**** embarrassing
Thanks and thanks for not saying what you are probably thinking.
Both you guys responding really gives me some stuff to work with!

 

[JoeMo]

**** embarrassing
Thanks and thanks for not saying what you are probably thinking.
Both you guys responding really gives me some stuff to work with!

You are welcome - thanks for the reply.