draw circle on chart at specified x,y coordinates

[abmaul]

I'm trying to draw a circle on a x,y scatter graph and can not figure it out. I'd like for it to call for a certain cell that sets the radius of the circle too. I've searched high and low on the internet for this answer with no luck.

 

[pgc01]

Hi

Not clear. You just want that for 1 specific point, or you want each point to be a circle with a radius specified by a cell? In this last case look at the bubble chart.

 

[abmaul]

Yes, I would like to draw an outlined circle in an existing x,y scatter plot at a specified x,y coordinate with a specific radius. A Bubble chart will not work for what I'm needing because my x,y scatter plot already has some points with connected lines between them.

 

[pgc01]

If the specified x,y coordinates corresponds to a point in the series then you can use the marker of the series. Use a circle marker, not filled, choose the size of the marker and the outline format.
If the specified x,y coordinates do not correspond to a point in the series add a circle shape.

 

[abmaul]

If the specified x,y coordinates corresponds to a point in the series then you can use the marker of the series. Use a circle marker, not filled, choose the size of the marker and the outline format.
If the specified x,y coordinates do not correspond to a point in the series add a circle shape.

The whole point of my question is because I don't know how to insert a shape into the chart! Could you please explain EXACTLY how to do this?

 

[pgc01]

Hi again

You should always post your excel version so that we know what's available to you.

If you are not using excel 2007 you should be able to see how to insert the circle just by recording the macro.

Anyway, this is an example.

This code inserts a circle with radius equal to a third of the smaller dimension of the chart object and with centre at the centre of the chart object.
It then tests the whole reference to the circle inside the chart.

In this code the circle is added to the first chart embedded in the first worksheet of the workbook.

Sub TestAddCircle()
Dim chto As ChartObject
Dim shp As Shape
Dim dWidth As Double, dHeight As Double, dRadius As Double


' set reference to the first chartobject in the first worksheet
Set chto = Worksheets(1).ChartObjects(1)

' the radius of the circle is a third of the smaller dimension of the chartobject
With chto
    dRadius = IIf(.Width > .Height, .Width, .Height) / 3
End With
    
' add the circle to the chart
With chto.Chart
    Set shp = .Shapes.AddShape(msoShapeOval, (chto.Width - dRadius) / 2, (chto.Height - dRadius) / 2, dRadius, dRadius)
    shp.Name = "TestCircle"
End With

' Test: paints the circle in green. Uses complete reference to the circle inside the chart
Worksheets(1).ChartObjects(1).Chart.Shapes("TestCircle").Fill.ForeColor.RGB = vbGreen
End Sub

You'll have to position and dimension the circle.
You can use its reference like I did in the last statement before the End Sub.

You can get the x,y values for the centre of the circle from the x,y values of the point, using the dimensions of the axes.
The radius of the circle, if I understood correctly, you'll read from some cell you have.

Is this what you wanted?

 

[abmaul]

Yes, thank you. I'm running Excel 2002. That VB code did insert a circle into my chart but I don't know where to reference the X and Y axis of where I want the circle located in my chart and I don't want it filled in with color (green). I also don't know how to reference which cell I want my radius to be. I just want an outline of a circle too. Sorry I'm new to VB.

 

[pgc01]

Yes, thank you. I'm running Excel 2002. That VB code did insert a circle into my chart but I don't know where to reference the X and Y axis of where I want the circle located in my chart and I don't want it filled in with color (green). I also don't know how to reference which cell I want my radius to be. I just want an outline of a circle too. Sorry I'm new to VB.

Hi

I thought you were solving the problem and had difficulties and that's why I posted the code, to get you started.

I see now that you expect someone to solve the problem for you, and that I cannot do.

Sorry, I cannot help you further.

 

[abmaul]

Hi

I thought you were solving the problem and had difficulties and that's why I posted the code, to get you started.

I see now that you expect someone to solve the problem for you, and that I cannot do.

Sorry, I cannot help you further.

I have an X and a Y coordinate on a chart currently. All I want is a way to draw a circle at this coordinate with a radius that I specify in another cell. That's it! Apparently this simple task is impossible in excel because I have yet to find a solution.

 

[pgc01]

... Apparently this simple task is impossible in excel because I have yet to find a solution.

No, that simple task is simple to implement. It just takes time, that I don't have, and someone to do it.

If you want to try to do it, this is an example I posted where I insert a shape in a bubble chart, with axes with variable lengths:

http://www.mrexcel.com/forum/excel-...-plot-area-via-visual-basic-applications.html