Plotting a diagonal in a bubble chart

[Matthew de Gale]

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

 

[Tushar Mehta]

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

 

[Jon Peltier]

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.aspx?scid=kb;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]

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

 

[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