Changing the Shape of a textbox in a chart in VBA.

[ziggeh]

Hi,

I am using Office 2007.

I was wondering if you were able to change the shape of the text box in VBA. I am trying to write some code which populates ovals on a chart with text inside. I can do this manually but when I record the macro it does not record the commands.

Also is there a good guide somewhere which covers how to manipulate textboxes in charts, for example boarders and font changes?

Thanks

 

[pgc01]

Hi ziggeh
Welcome to the board

To add a shape to a chart you just add it to its shapes collection. Once you have the reference of the shape, you manipulate as usual.

This is an example. You have a chart object named "Chart 1" in Sheet1. This code adds an oval to the chart, adds text to the oval and does some fomatting to the oval and to the text.

Sub Macro1()
Dim cht As Chart
Dim shp As Shape
 
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    
    Set shp = cht.Shapes.AddShape(msoShapeOval, 50, 50, 120, 50)
    
    With shp
        .Name = "Oval 1"

        With .DrawingObject
            .Text = "Some Text"
            With .Font
                .Size = 12
                .Color = RGB(0, 0, 255)
                .Bold = True
            End With
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With
 
        .Fill.ForeColor.RGB = RGB(0, 255, 0)

        With .Line
            .ForeColor.RGB = RGB(255, 0, 0)
            .Weight = 2
        End With
    End With
    
End Sub

 

[ziggeh]

Wow thanks a lot, thats really useful.

How do you guys find out these things?

I tried to look at the object browser in "help" but couldn't find this stuff.

 

[pgc01]

I'm glad it helped.

If you look at the Chart object properties you'll see there the Shapes collection. This means that you can add a shape to the chart as you would to a worksheet.

As you have noticed the macro recorder in xl2007 is very bad. It was much more powerful in prior versions. Let's hope this will improve with the SP2.

 

[mooseman]

I just love it when I find an answer here to a question I haven't even asked yet.