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]

Hi Ovidio

As I can see the origin of coordinates is in the upper left corner and it's expresed in pixels, isn't it.

No, that would be no good since it would depend on the resolution of your monitor and would be different on another monitor.

The measures in the chart are in points. The Point is a typographical measure equal to 1/72 of an inch. Very used in charts, drawings and text.
For ex. in the text fonts like Calibri 11pt.

... so the added shapes could be dawed using the (x,y) coordinate system of the graph ....

You can use the location and dimension of the axes to position the shape.

This is an example that draws a rectangle in the middle of the plot area.
The width is half the width of the X axis
The height is half the height of the Y axis
It is positioned in the center of the plot area.

It also names the rectangle and then refers to it by name and paints the interior yellow.

Try this code:

Sub ChartAddRectCentre()
Dim cht As Chart
Dim XAxis As Axis, YAxis As Axis
Dim shp As Shape
Dim dRLeft As Double, dRTop As Double, dRWidth As Double, dRHeight As Double

Set cht = ActiveSheet.ChartObjects(1).Chart
Set XAxis = cht.Axes(xlCategory)
Set YAxis = cht.Axes(xlValue)

' calculate the parameters for a the rectangle in the middle of the plot area with half width and height of the axes
dRLeft = XAxis.Left + XAxis.Width / 4
dRWidth = XAxis.Width / 2
dRTop = YAxis.Top + YAxis.Height / 4
dRHeight = YAxis.Height / 2

' draw the rectangle
Set shp = cht.Shapes.AddShape(msoShapeRectangle, dRLeft, dRTop, dRWidth, dRHeight)
shp.Name = "MyRect"

' refer to the rectangle by name
cht.Shapes("MyRect").Fill.ForeColor.RGB = vbYellow
End Sub

 

[QuijoteMx]

Thank you. I'll try it ...