Create Bubble Chart Automatically

[Excel Fox]

Spare the need to pick each series of the bubble chart manually. Here's a quick and easy way to create a bubble chart.

Just specify the source range in your VBA code your chart is ready. This is only the basic layout. Anything custom has been left out to discretion of users.

[Rasm]

Hey Fox
I get an error - iy simply shows a RED X with the number 400 - I am running xl2007

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg.A0opm95t2XEA0q3Kshmu uY (https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg.A0opm95t2XEA0q3Kshmu uY)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg (https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836 (https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195)
https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110 (https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Admin]

Hi,

Replace the CallAB macro with this one.


Sub CallAB()

On Error Resume Next
ActiveSheet.ChartObjects.Delete
On Error GoTo 0
ActiveSheet.Shapes.AddChart 'For Excel 2007+ Only
AssignBubbleSource ActiveSheet.ChartObjects(1), ActiveSheet.Range("A1:D5")

End Sub

[Excel Fox]

Revised file with the above corrections

[PC_BABE]

Hi,

The deafult labeling for the chart is "Y Value". I want it to be NameColumn.

Ive tried several variations of but it crashes. Can you sugget a fix? Thnx


Next lngRow
With chtBblChart.Chart
.ChartType = xlBubble3DEffect
.ApplyDataLabels = xlDataLabelsShowValue [ShowNameSeries]

.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis) 'For Excel 2007+ Only
.SetElement (msoElementPrimaryValueAxisTitleRotated) 'For Excel 2007+ Only
.SetElement (msoElementDataLabelRight) 'For Excel 2007+ Only
If blnHeader Then
.Axes(1, 1).AxisTitle.Text = rngChartSource.Cells(1, rngChartSource.Column + FirstColumn).value
.Axes(2, 1).AxisTitle.Text = rngChartSource.Cells(1, rngChartSource.Column + SecondColumn).value
End If
End With

[PC_BABE]

I posted earlier, but I trhink I did it as a reply. Im trying to change the lable value. It currently defaluts to the Y Value. Id like it to default againt the NameColumn. When I tried ApplyDataLable = xlDataLabel it crashes. Any thoughts?

[Admin]

Hi

Add these line of codes before the End With of the code you posted above.


Dim chtSeries As SeriesCollection
Dim lngSrsLoop As Long

Set chtSeries = .SeriesCollection

For lngSrsLoop = 1 To chtSeries.Count
If chtSeries(lngSrsLoop).DataLabels(1).ShowCategoryNa me Then chtSeries(lngSrsLoop).DataLabels(1).ShowCategoryNa me = False
If chtSeries(lngSrsLoop).DataLabels(1).ShowValue Then chtSeries(lngSrsLoop).DataLabels(1).ShowValue = False
If Not chtSeries(lngSrsLoop).DataLabels(1).ShowSeriesName Then chtSeries(lngSrsLoop).DataLabels(1).ShowSeriesName = True
Next

[PC_BABE]

Be still my heart ... worked like a charm! Thank you, Thank You.