Thread: 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 <Oha0AJWgFHA.2156@TK2MSFTNGP14.phx.gbl>, matthew@tips.org.za
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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1d35b3079ec18a298b0b3@msnews.microsoft.com...
> 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 <Oha0AJWgFHA.2156@TK2MSFTNGP14.phx.gbl>, matthew@tips.org.za
> 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" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
news:eFpU7gogFHA.1252@TK2MSFTNGP09.phx.gbl...
> 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
>>