Shapes.AddShape(msoShapeRectangle) Properties (2000 / SR1)

Topic

New Reply

I am attemping to create a textbox on a chart in an Excel worksheet. I can manage to create the rectangle and define the position, size, border, foreground fill colour and text so far, but I can’t seem to set the internal alignment or font size, weight, etc. Here is the code I have so far:

Lines ending with the following, do not work: ‘ DOES NOT WORK! Can someone please advise what I am doing wrong?

Thanks,

Drew

    With Sheets("Rate Chart").Shapes.AddShape(msoShapeRectangle, 460, 270, 160, 30)
        .AutoSize = True     ' DOES NOT WORK!
        .HorizontalAlignment = xlCenter     ' DOES NOT WORK!
        .VerticalAlignment = xlCenter     ' DOES NOT WORK!
        .Name = "RateBox"
        .TextFrame.Characters.Text = "Here is some test text"
        .Line.Visible = 0
        .Fill.ForeColor.RGB = RGB(255, 255, 225)
        .TextFrame.Characters.Font = "Verdana"     ' DOES NOT WORK!
        .TextFrame.TextRange.Text.Size = 14     ' DOES NOT WORK!
        .TextFrame.TextRange.Text.Bold = msoTrue     ' DOES NOT WORK!
        .MarginBottom = 8
        .MarginLeft = 10
        .MarginRight = 10
        .MarginTop = 8
    End With

Reply | Quote

Replies

Many of the properties are not properties of a rectangle, try the textbox:

    With Sheets("Rate Chart").Shapes.AddTextbox(msoTextOrientationHorizontal, 460, 270, 160, 30)
        .Name = "RateBox"
        .Line.Visible = 0
        .Fill.ForeColor.RGB = RGB(255, 255, 225)
        With .TextFrame
            .AutoSize = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .MarginBottom = 8
            .MarginLeft = 10
            .MarginRight = 10
            .MarginTop = 8
            With .Characters
                .Text = "Here is some test text"
                With .Font
                    .FontStyle = "Verdana"
                    .Size = 14
                    .Bold = msoTrue
                End With
            End With
        End With
    End With

Steve

Reply | Quote

Many of the properties are not properties of a rectangle, try the textbox:

    With Sheets("Rate Chart").Shapes.AddTextbox(msoTextOrientationHorizontal, 460, 270, 160, 30)
        .Name = "RateBox"
        .Line.Visible = 0
        .Fill.ForeColor.RGB = RGB(255, 255, 225)
        With .TextFrame
            .AutoSize = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .MarginBottom = 8
            .MarginLeft = 10
            .MarginRight = 10
            .MarginTop = 8
            With .Characters
                .Text = "Here is some test text"
                With .Font
                    .FontStyle = "Verdana"
                    .Size = 14
                    .Bold = msoTrue
                End With
            End With
        End With
    End With

Steve

Reply | Quote

You are trying to set a lot of properties that do not exist for the object you’re manipulating. It helps to define object variables, so that IntelliSense can assist you, instead of just groping in the dark. Moreover, you can set some text properties only after you have set the text, they aren’t valid if there is no text yet.

Dim shp As Shape
Dim tf As TextFrame
Set shp = Sheets(“Blad1”).Shapes.AddShape(msoShapeRectangle, 460, 270, 160, 30)
With shp
.Name = “RateBox”
.Line.Visible = 0
.Fill.ForeColor.RGB = RGB(255, 255, 225)
Set tf = .TextFrame
With tf
tf.Characters.Text = “Here is some test text”
.AutoSize = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Characters.Font.Name = “Verdana”
.Characters.Font.Size = 14
.Characters.Font.Bold = True
.MarginBottom = 8
.MarginLeft = 10
.MarginRight = 10
.MarginTop = 8
End With
End With
Set tf = Nothing
Set shp = Nothing

Reply | Quote

Thank you Hans and Steve for your assistance. Turns out, I apparently solved the ‘riddle’ before I got your answers. First time that’s ever happened. Here is what I came up with on my own before I read your posts:

    With Sheets("Rate Chart").Shapes.AddShape(msoShapeRectangle, 460, 270, 175, 30)
        .Name = "RateBox"
        With .TextFrame.Characters
                .Text = "Rate = " & Range("Spring_Rate_Result").Text & " lbs/in"
                .Font.Bold = True
                .Font.Name = "Verdana"
                .Font.Size = 14
        End With
        .TextFrame.HorizontalAlignment = xlHAlignCenter
        .TextFrame.VerticalAlignment = xlVAlignCenter
        .Line.Visible = 0
        .Fill.ForeColor.RGB = RGB(255, 255, 215)
    End With

Basically the same result. Thank you for your help just the same. This Lounge has been a GREAT tool for me!!

Drew

Reply | Quote

Thank you Hans and Steve for your assistance. Turns out, I apparently solved the ‘riddle’ before I got your answers. First time that’s ever happened. Here is what I came up with on my own before I read your posts:

    With Sheets("Rate Chart").Shapes.AddShape(msoShapeRectangle, 460, 270, 175, 30)
        .Name = "RateBox"
        With .TextFrame.Characters
                .Text = "Rate = " & Range("Spring_Rate_Result").Text & " lbs/in"
                .Font.Bold = True
                .Font.Name = "Verdana"
                .Font.Size = 14
        End With
        .TextFrame.HorizontalAlignment = xlHAlignCenter
        .TextFrame.VerticalAlignment = xlVAlignCenter
        .Line.Visible = 0
        .Fill.ForeColor.RGB = RGB(255, 255, 215)
    End With

Basically the same result. Thank you for your help just the same. This Lounge has been a GREAT tool for me!!

Drew

Reply | Quote

You are trying to set a lot of properties that do not exist for the object you’re manipulating. It helps to define object variables, so that IntelliSense can assist you, instead of just groping in the dark. Moreover, you can set some text properties only after you have set the text, they aren’t valid if there is no text yet.

Dim shp As Shape
Dim tf As TextFrame
Set shp = Sheets(“Blad1”).Shapes.AddShape(msoShapeRectangle, 460, 270, 160, 30)
With shp
.Name = “RateBox”
.Line.Visible = 0
.Fill.ForeColor.RGB = RGB(255, 255, 225)
Set tf = .TextFrame
With tf
tf.Characters.Text = “Here is some test text”
.AutoSize = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Characters.Font.Name = “Verdana”
.Characters.Font.Size = 14
.Characters.Font.Bold = True
.MarginBottom = 8
.MarginLeft = 10
.MarginRight = 10
.MarginTop = 8
End With
End With
Set tf = Nothing
Set shp = Nothing

Reply | Quote