Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

    <pre> 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

    </pre>


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

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

    <pre> With Sheets("Rate Chart").Shapes.AddTextbox(msoTextOrientationHorizo ntal, 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</pre>


    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <pre> 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
    </pre>


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

    Drew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •