Bubble Chart Legend

Andy, thanks for all your assistance. Now I have two ways to display the
chart, whether the data is in a table or not.

I appreciate all your help and everything worked well.
--
I Teach


"Andy Pope" wrote:

If the data is not in a table then you can use something like

Set rngData = ActiveSheet.Range("A1").currentregion


Of course you would need to amend the row/column indexes so you got a
reference to the correct data.

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
news
This worked perfectly - I cannot thank you enough. My only question is
that
it only works when the data is formatted as a table. When I convert it to
a
range, I get an error message "Subscript out of range" and in debug, it
points to the line "Set rngData = ActiveSheet.ListObjects(1).Range". Could
this macro work without the data being formatted as a table. If not,
that's
okay too.

Please let me know.
--
I Teach


"Andy Pope" wrote:

Based on Herbert's data try this, which will create a new chart and
populate
with a series for each NName.
Blank NNames are ignored.

Sub x()

Dim objCht As Chart
Dim rngData As Range
Dim lngRow As Long

Set objCht = ActiveSheet.ChartObjects.Add(100, 100, 300, 200).Chart
Set rngData = ActiveSheet.ListObjects(1).Range
Set rngData = rngData.Offset(1, 0).Resize(rngData.Rows.Count - 1)
objCht.ChartType = xlBubble
For lngRow = 1 To rngData.Rows.Count
If Len(rngData.Cells(lngRow, 4)) 0 Then
With objCht.SeriesCollection.NewSeries
.Name = rngData.Cells(lngRow, 4)
.XValues = rngData.Cells(lngRow, 1)
.Values = rngData.Cells(lngRow, 2)
.BubbleSizes = rngData.Cells(lngRow, 3)
End With
End If
Next

End Sub

After the code is run check the Select Data dialog to see the range
references each series has.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"I Teach" wrote in message
...
Hi Andy,

Here is the macro from Herbert Seidenberg to create data labels next to
each
bubble. Could you please help me to change the macro so that a legend
is
created on the right, rather than data labels.

Thanks for your assistance.


Sub PointLabel()

Dim m As Variant
Dim i As Integer
Dim y As Integer

With Sheets("Data").ListObjects("Table1")
y = .ListRows.Count
End With
With Sheets("Data")
m = Range("Table1[NName]")
End With
With Sheets("Bubble").SeriesCollection(1)
.ApplyDataLabels
For i = 1 To y
.Points(i).DataLabel.Text = m(i, 1)
Next i
End With

End Sub
--
I Teach


"Andy Pope" wrote:

Hi,

Right-click the chart and pick Select Data. You can use the Add button
to create new series and pick the cell for Name and Values.

Cheers
Andy

I Teach wrote:
Thanks, Andy. Please tell me how to do that in Excel 2007.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.


.


.