Add shape to fixed position in a chart

[hansV]

Hi guys,
I've used the information on this board quite often, first time to ask a question myself. I didn't seem to find anything simular on the board and hope I formulate this question clearly and in the correct form...

My problemconcerns the positioning of a shape in a chart. The chart itself is part of aworksheet. The chart is an XY scatter plot. The origin of the X and Y-axis isin the left –top corner of the chart, the X-axis is from left to right, theY-axis is from top to bottom. I try to add a rectangle next to the chart, startingat the same height as my X-axis and the length should be the same as the lengthof the chart.
Toaccomplish this I tried first to obtain the position of the chart in theworksheet and afterwards the position of the origin of the X and Y-axis in thechart and also the width and height of the chart. Alas, I seem to be doingsomething wrong as I don’t get the expected result and I cannot seem to figureout what values I obtain for the parameters above.
My code:

Dim ws AsWorksheet
Dim p AsPlotArea
Dim c AsChart
Dim H, W,L, T, hi, wi, Li, ti As Double

Set ws =ActiveSheet
Set c =ws.ChartObjects("Grafiek 1").Chart
Set p =c.PlotArea
H =p.height 'height of chart?
W =p.width 'width of charT?
L =p.left 'X-position of top left corner ofchart?
T =p.top 'Y-position of top left cornerof chart?
hi =p.InsideHeight 'height of Y-axis?
wi =p.InsideWidth ‘height of X-axis?
Li =p.InsideLeft 'X-position of origin of Xand Y-axis?
ti =p.InsideTop 'Y-position of origin of Xand Y-axis?
Set shp =ws.Shapes.AddShape(msoShapeRectangle, L + Li, T + ti, 20, hi)

I hope someone can help me.

thank you all in advance,
HansV

 

[pgc01]

Hi Hans
Welcome to the board

Sorry, not clear.
The title says "add shape in a chart" but then the text says "add a rectangle next to the chart"

Do you want
- to add a shape to the worksheet next (or over) the chart
or
- to add a shape to the chart (a shape inside the chart object)
?

 

[hansV]

Hi pgc01,

I want to add a rectangular shape in the worksheet next (or over) the chart.

Thanks for your help

 

[pgc01]

Hi

This is an example.
Assuming you have in worksheet Sheet1 a chart object named MyChart, this example add a rectangle to the worksheet just under the chart object, with half witdth and height.

Try:

Sub Test()
Dim ws As Worksheet
Dim chtO As ChartObject
Dim shp As Shape

Set ws = Worksheets("Sheet1")
Set chtO = ws.ChartObjects("MyChart")

With chtO
    Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
        .Left, .Top + .Height, .Width / 2, .Height / 2)
End With

End Sub

 

[pgc01]

Sorry, you may have noticed that there's a typo in the code

Where you see "ActiveSheet.Shapes..." should be "ws.Shapes..."

 

[hansV]

Hi pgc01,

this seems to work as i hoped. Thx for the help!

 

[pgc01]

You're welcome.