Adding shapes to Chart Plot Area via VBA...

[Matty]

Hi All,

I would like to add a square shape to a Chart Plot Area starting at where the vertical and horizontal axes meet. If my data was static then this would be a simple job, but the data is dynamic (and so are the axes) and therefore I need the square to grow and shrink depending on what is shown.

Is it possible to use some code to do this?

Thanks,

Matty

 

[pgc01]

I'm glad it's working OK. Thanks for the feedback.

 

[Matty]

Hi again,

For whatever reason, on the real chart I'm applying this to, the rectangle is slightly off where it should be. I suspect this is because it's had quite a bit of formatting added and axes moved, etc.

Is there a way to default the plot area back to its original position so that the rectangle lands where it should be?

Thanks,

Matty

 

[pgc01]

Hi Matty

I think there are 2 issues here.

For whatever reason, on the real chart I'm applying this to, the rectangle is slightly off where it should be. I suspect this is because it's had quite a bit of formatting added and axes moved, etc.

1 - if you are adding the rectangle as the last thing you do, then the rectangle should be in the right place, since you are reading the current location of the axes. I have tried to reproduce your problem (added axes titles, resized the plot area) but, at the end, when I added the rectangle it was in the right position.

Is there a way to default the plot area back to its original position so that the rectangle lands where it should be?

2- I guess you could store the original position and size of the plot area and then use those values to reposition and resize it.

 

[Matty]

Hi pgc01,

Thanks for your continued support.

Rather than having to recreate the chart, I added in some 'adjustment' values to the code, playing with various values until the rectangle landed exactly where required.

Like you, if I create a fresh chart and run the code, things work exactly as desired, but the chart I already have is proving stubborn. If I get chance, I will re-create it again which should sort the problem...

Cheers,

Matty

 

[pgc01]

I'm glad you have it working OK. Cheers!

 

[Matty]

For the sake of completion, just to confirm that the alignment issue was a result of the Zoom option not being at 100%.

By changing the Zoom % back to 100%, running the macro and then changing the Zoom back to the desired %, alignment was perfect.

Hopefully anyone with a similar issue will now not fall for the same problem that I experienced.

Thanks again, pgc01.

Matty

 

[QuijoteMx]

Hi!
I looked into the web looking for hints to draw objects in an excel graph with vba and found this post. It's very useful. I adapted to draw lines changing rectangles.add by lines.add and got an error.
The assistant does not provide help with the kind of shapes you could add. In fact, rectangles does not appear as a method o property in the object chart.
What is wrong ...

O. Molina

 

[pgc01]

Hi Quijote
Welcome to the board

This way of adding a rectangle is an old way, Today you usually add a shape to a sheet.
You can see the chart as a chart sheet inside a ChartObject. You can add shapes the usual way.

This is a simple example to add a line and a rectangle inside a chart in the active sheet in a way you'll recognise.

Try in a sheet with a chart:

Sub AddShapes2Chart()
Dim cht As Chart

Set cht = ActiveSheet.ChartObjects(1).Chart
    
With cht.Shapes
    .AddConnector msoConnectorStraight, 10, 10, 100, 10
    .AddShape msoShapeRectangle, 10, 50, 100, 50
End With
End Sub

 

[pgc01]

I adapted to draw lines changing rectangles.add by lines.add and got an error.

... and, BTW, that should also work, that's the old way to add a line.
Try executing this statement:

ActiveSheet.ChartObjects(1).Chart.Lines.Add 10, 1, 50, 50

 

[QuijoteMx]

Thanks, works smooth. Just one thing else. As I can see the origin of coordinates is in the upper left corner and it's expresed in pixels, isn't it. Do you know
a way to convert axes units to pixels so the added shapes could be dawed using the (x,y) coordinate system of the graph ....

Thanks,
Ovidio Molina