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/
_______
