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

 

[Matty]

The error bars method works for me? Doesn't it work for you?

It does, but only to a point. The problem is that it only gives me lines, when what I want is full square. Moreover, because my x axis is set to Auto, the horizontal error bar doesn't always extend right over to the y axis (my chart contains both negative and positive values along the x axis, and when set to Auto, Excel does what it wants in terms of setting the lowest values to show on the axis).

That's unless I'm completely missing something...

Cheers,

Matty

 

[Matty]

Hi again

What's the chart type?
For many chart types, another way that will draw the rectangle is simply to add a series with 5 points (the corners + the first corner)

For ex., add a series with

(0,0)
(0,1)
(1,1)
(1,0)
(0,0)

This line will draw a rectangle that starts where the the axes meet, like you said you want.

I'm using a Bubble chart, which doesn't seem compatible with other chart types. Your suggestion was the first thing I tried, but I soon realised this wasn't going to work due to the compatibility issues.

Cheers,

Matty

 

[pgc01]

OK. If this is not solved until tonight (GMT) I'll try to post an example of how to add the shape.
I guess you tried Andrew's suggestion and it doesn't solve your problem?

 

[Andrew Poulsom]

Please post some sample data and specify where you want the square.

 

[pgc01]

Please post some sample data and specify where you want the square.

Yes, that will be helpful, also the dimensions of the rectangle.

 

[Matty]

Hi Chaps,

Below is is an example of what I'm after:

The problem is the fact that both the x and y axes will change, yet always need to reference 0 on x axis and 8 and on the y axis and fill the entire gap in between.

Hope this makes sense.

Cheers,

Matty

 

[pgc01]

Hi Matty

I assumed a chart named MyChart in Sheet1.

This is an example based on the chart you posted, that you can adapt.

Sub ChartAddRect()
Dim cht As Chart
Dim XAxis As Axis, YAxis As Axis
Dim rect As Rectangle
Dim dRLeft As Double, dRTop As Double, dRWidth As Double, dRHeight As Double

Set cht = Worksheets("Sheet1").ChartObjects("MyChart").Chart
Set XAxis = cht.Axes(xlCategory)
Set YAxis = cht.Axes(xlValue)

' position and location of the rectangle
dRLeft = XAxis.Left
dRWidth = (0 - XAxis.MinimumScale) / (XAxis.MaximumScale - XAxis.MinimumScale) * XAxis.Width
dRHeight = (8 - YAxis.MinimumScale) / (YAxis.MaximumScale - YAxis.MinimumScale) * YAxis.Height
dRTop = YAxis.Top + YAxis.Height - dRHeight

' draw the rectangle
Set rect = cht.Rectangles.Add(dRLeft, dRTop, dRWidth, dRHeight)
rect.Name = "MyRect"

End Sub

 

[Matty]

This is an example based on the chart you posted, that you can adapt.

Thanks pgc01 - it works well!

If I need to adapt the code to create the square in the top right (so 8 - 14 on the y axis and 0 - 25 on the x axis) instead of the bottom left, what would this look like? I've had a play myself, but can't quite get the syntax right.

Thanks,

Matty

 

[pgc01]

Hi Matty

Try:

' position and location of the rectangle
dRWidth = (XAxis.MaximumScale - 0) / (XAxis.MaximumScale - XAxis.MinimumScale) * XAxis.Width
dRHeight = (YAxis.MaximumScale - 8) / (YAxis.MaximumScale - YAxis.MinimumScale) * YAxis.Height
dRTop = YAxis.Top
dRLeft = XAxis.Left + XAxis.Width - dRWidth

 

[Matty]

Thanks pgc01 - working flawlessly.

Matty