Bubble Chart Legend

I

I Teach

Is it possible to create a bubble chart with the standard X, Y, and Bubble
Size values and then use text in another column as the legend, such as
Company A as the legend with the values in Growth Rate, Margin, and Bubble
Size?

Thanks for your assistance.
 
I

I Teach

Thanks for the information, but what I was looking for was how to create
those labels as a legend. Thanks again.
 
A

Andy Pope

Hi,

The legend contains the Names of each series. So you would need to create a
series for each Company.
There is a limit of 255 series.

Cheers
Andy
 
A

Andy Pope

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

I Teach

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
 
A

Andy Pope

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 said:
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 said:
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



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
.
Click to expand...
Click to expand...
 
I

I Teach

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 said:
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 said:
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 said:
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
.
Click to expand...
Click to expand...

.
Click to expand...
 
A

Andy Pope

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 said:
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 said:
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 said:
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


:

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
.
Click to expand...

.
Click to expand...
Click to expand...
 
I

I Teach

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 said:
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 said:
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 said:
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
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


:

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
.


.
Click to expand...
Click to expand...

.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to create a bubble chart with more than 2 variables 0
Problem with BUBBLE CHART scale and legend 6
Making a Bubble Chart with 4 variables instead of 3 1
Creating Bubble Charts 1
Bubble charts: just size, no x/y 2
change bubble order in a bubble chart 1
How to label bubbles in bubble chart 4
Bubble chart question 2

Top