Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
Larry B.
 
Posts: n/a
Default How do I name the Bubbles on my Bubble chart.

I created a Bubble Chart using the help feature but I need to name the three
Bubbles. I don't know how to lay it out in excel and then copy the range to
accomplish this.
  #2   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default How do I name the Bubbles on my Bubble chart.

The easiest way to give the bubbles different names and have the names
appear in data labels is to create three different bubble series. The data I
used in a little demo is like this:

Bubble A
1 1 1

Bubble B
2 2 2

Bubble C
3 3 3

Each mini range contains the series name in the middle cell of the top row,
with X, Y, and bubble size values in the bottom row.

Select the block for series A (all 2x3, including the blank cells), and
create a bubble chart. Copy the block for series B (2x3), select the chart,
and Paste. Copy the block for C, select the chart, and paste. On the Chart
menu, choose Chart Options, and on the Data Labels tab, select Series Name.

It's funny you should ask this today. This morning I came across a procedure
I'd posted to the newsgroups a couple years ago which automated the process.
Set up your data like this, with four columns for (left to right) Name, X,
Y, and bubble size:

Bubble A 1 4 1
Bubble B 2 3 2
Bubble C 3 2 3
Bubble D 4 1 4

Select the data range and run this macro:

Sub OneRowPerBubbleSeries()
'' Takes 4-column range and constructs Bubble chart
'' Uses one series per row: Columns in order: Name, X, Y, Bubble Size
Dim wks As Worksheet
Dim cht As Chart
Dim srs As Series
Dim rng As Range
Dim rng1 As Range
Dim rownum As Integer
Dim bFirstRow As Boolean

Set wks = ActiveSheet
Set rng = Selection
Set cht = wks.ChartObjects.Add(100, 100, 350, 225).Chart
bFirstRow = True
For rownum = 1 To rng.Rows.Count
Set rng1 = rng.Cells(rownum, 2).Resize(1, 3)
If IsNumeric(rng1.Cells(1, 1).Value) And _
IsNumeric(rng1.Cells(1, 2).Value) And _
IsNumeric(rng1.Cells(1, 3).Value) Then

'' First time: need to do it differently
If bFirstRow Then
cht.SetSourceData Source:=rng1, _
PlotBy:=xlColumns
cht.ChartType = xlBubble
bFirstRow = False
'' Remove spurious second series
cht.SeriesCollection(2).Delete
Else
Set srs = cht.SeriesCollection.NewSeries
End If

With cht.SeriesCollection(cht.SeriesCollection.Count)
'' Values, XValues, Name all take range
'' BubbleSizes takes address in R1C1 format
.Values = rng1.Cells(1, 2)
.XValues = rng1.Cells(1, 1)
.BubbleSizes = "=" & rng1.Cells(1, 3).Address _
(ReferenceStyle:=xlR1C1, external:=True)
.Name = rng.Cells(rownum, 1)
End With

End If
Next
End Sub


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



"Larry B." <Larry wrote in message
...
I created a Bubble Chart using the help feature but I need to name the
three
Bubbles. I don't know how to lay it out in excel and then copy the range
to
accomplish this.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i add text labels to each bubble on bubble chart? bazza Charts and Charting in Excel 1 December 7th 05 05:02 AM
customizing the mouse-over display in a bubble chart Mark Charts and Charting in Excel 2 August 9th 05 12:01 AM
Can I keep the bubbles in my bubble chart from overlapping? B_Gr Charts and Charting in Excel 1 May 21st 05 05:56 PM
ho to change in the bubble chart the bubble position and size laszlo Charts and Charting in Excel 0 March 25th 05 05:45 PM
How do I move a hidden bubble to the front in a bubble chart in E. Scott Excel Discussion (Misc queries) 0 February 20th 05 08:55 PM


All times are GMT +1. The time now is 04:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"