Plotting a diagonal in a bubble chart
 

HI

I need to plot a diagonal in a bubble chart. It is dynamically generated
otherwise I'd simply manually draw the line! As you can't (I think) use two
chart types with bubble charts, generating a line drawing object seem the
best approach. What I'd like to do is to generate a drawing object that
takes its co-ordinates from a series on the chart.
SO, if I draw it manually and record a macro I get:

ActiveChart.Shapes.AddLine(210#, 55.5, 277.5, 540#)

I need to replace the absolute values with ones generated by a data series.
At the moment the best I can do is generate very small bubbles to form a
slightly surreal diagonal of dots through the chart, but what I really need
is a clean line that goes to the edge of the chart.

I'm sure I haven't expressed this very well but any help in matching data
series co-ordinates and drawing object co-ordinates would be much
appreciated.

Thanks

Matthew de Gale

Why not add another series? Set the bubble size to a very small
number. IIRC, zero doesn't work, so you'd have to use 1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
HI

I need to plot a diagonal in a bubble chart. It is dynamically generated
otherwise I'd simply manually draw the line! As you can't (I think) use two
chart types with bubble charts, generating a line drawing object seem the
best approach. What I'd like to do is to generate a drawing object that
takes its co-ordinates from a series on the chart.
SO, if I draw it manually and record a macro I get:

ActiveChart.Shapes.AddLine(210#, 55.5, 277.5, 540#)

I need to replace the absolute values with ones generated by a data series.
At the moment the best I can do is generate very small bubbles to form a
slightly surreal diagonal of dots through the chart, but what I really need
is a clean line that goes to the edge of the chart.

I'm sure I haven't expressed this very well but any help in matching data
series co-ordinates and drawing object co-ordinates would be much
appreciated.

Thanks

Matthew de Gale

Matthew -

Maybe it's overkill, but Microsoft has a VBA technique for making an XY
chart with custom sized markers, so it's like a bubble chart. It's on
this page:

http://support.microsoft.com/default...b;en-us;139662

You could make a pseudo bubble series with this technique, then draw the
line with a second XY series with two points.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Matthew de Gale wrote:

HI

I need to plot a diagonal in a bubble chart. It is dynamically generated
otherwise I'd simply manually draw the line! As you can't (I think) use two
chart types with bubble charts, generating a line drawing object seem the
best approach. What I'd like to do is to generate a drawing object that
takes its co-ordinates from a series on the chart.
SO, if I draw it manually and record a macro I get:

ActiveChart.Shapes.AddLine(210#, 55.5, 277.5, 540#)

I need to replace the absolute values with ones generated by a data series.
At the moment the best I can do is generate very small bubbles to form a
slightly surreal diagonal of dots through the chart, but what I really need
is a clean line that goes to the edge of the chart.

I'm sure I haven't expressed this very well but any help in matching data
series co-ordinates and drawing object co-ordinates would be much
appreciated.

Thanks

Matthew de Gale

Thank you for your response: I have done this - its my best solution so
far. Its not ideal though as no matter how much I play witrh the
formatting, it still doesn't look clearly like a line - and I also can't get
it to go all the way up through the chart (although I can probably spend
more time on changing the scale to sort this out.

Thanks

Matthew


"Tushar Mehta" wrote in message
m...
Why not add another series? Set the bubble size to a very small
number. IIRC, zero doesn't work, so you'd have to use 1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
HI

I need to plot a diagonal in a bubble chart. It is dynamically generated
otherwise I'd simply manually draw the line! As you can't (I think) use
two
chart types with bubble charts, generating a line drawing object seem
the
best approach. What I'd like to do is to generate a drawing object that
takes its co-ordinates from a series on the chart.
SO, if I draw it manually and record a macro I get:

ActiveChart.Shapes.AddLine(210#, 55.5, 277.5, 540#)

I need to replace the absolute values with ones generated by a data
series.
At the moment the best I can do is generate very small bubbles to form a
slightly surreal diagonal of dots through the chart, but what I really
need
is a clean line that goes to the edge of the chart.

I'm sure I haven't expressed this very well but any help in matching data
series co-ordinates and drawing object co-ordinates would be much
appreciated.

Thanks

Matthew de Gale

HI Jon

Thank you very much for replying. I have looked at this and realised (with
something of a sigh!) that it may be my best solution.
What held me back is that I have seen an example of what I want being done:
ie mapping drawing objects dynamically accordoing to the data) and noted
some very useful effects with it (for example drawing polygon shapes to map
out differently coloured segments of a chart according to the data), and
suspect I may need to do this for several other projects. I haven't done too
much vba coding in Excel before so I was (like everyone, I guess!) looking
for a shortcut before completely figuring it out on my own.
Again, I appreciate your response.

Matthew



"Jon Peltier" wrote in message
...
Matthew -

Maybe it's overkill, but Microsoft has a VBA technique for making an XY
chart with custom sized markers, so it's like a bubble chart. It's on this
page:

http://support.microsoft.com/default...b;en-us;139662

You could make a pseudo bubble series with this technique, then draw the
line with a second XY series with two points.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Matthew de Gale wrote:

HI

I need to plot a diagonal in a bubble chart. It is dynamically generated
otherwise I'd simply manually draw the line! As you can't (I think) use
two chart types with bubble charts, generating a line drawing object
seem the best approach. What I'd like to do is to generate a drawing
object that takes its co-ordinates from a series on the chart.
SO, if I draw it manually and record a macro I get:

ActiveChart.Shapes.AddLine(210#, 55.5, 277.5, 540#)

I need to replace the absolute values with ones generated by a data
series. At the moment the best I can do is generate very small bubbles to
form a slightly surreal diagonal of dots through the chart, but what I
really need is a clean line that goes to the edge of the chart.

I'm sure I haven't expressed this very well but any help in matching data
series co-ordinates and drawing object co-ordinates would be much
appreciated.

Thanks

Matthew de Gale