Results 1 to 8 of 8

Thread: Create Bubble Chart Automatically

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10

    Create Bubble Chart Automatically

    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.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #2
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    13
    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/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg. A0opm95t2XEA0q3KshmuuY
    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=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=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=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=314229#p314229
    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=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=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=40533&p=313971#p313971
    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.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 03-11-2024 at 02:05 PM.
    xl2007 - Windows 7
    xl hates the 255 number

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi,

    Replace the CallAB macro with this one.

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

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Revised file with the above corrections
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0
    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

    Code:
        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
    Last edited by Admin; 08-06-2012 at 04:04 PM.

  6. #6
    Junior Member
    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0
    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?

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

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

    Code:
        Dim chtSeries       As SeriesCollection
        Dim lngSrsLoop      As Long
        
        Set chtSeries = .SeriesCollection
        
        For lngSrsLoop = 1 To chtSeries.Count
            If chtSeries(lngSrsLoop).DataLabels(1).ShowCategoryName Then chtSeries(lngSrsLoop).DataLabels(1).ShowCategoryName = 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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Junior Member
    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0

    Thank you

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

Similar Threads

  1. Automatically Create Multiple Dynamic Named Ranges Using VBA
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 04-24-2013, 03:49 PM
  2. Bubble Chart
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 1
    Last Post: 09-04-2012, 09:01 AM
  3. Copy Automatically Between Two Worksheets
    By marreco in forum Excel Help
    Replies: 0
    Last Post: 08-27-2012, 04:48 PM
  4. Bubble Sort Function
    By PcMax in forum Excel Help
    Replies: 5
    Last Post: 12-15-2011, 11:12 AM
  5. UDF to Create In-Cell Chart in Excel
    By Admin in forum Download Center
    Replies: 0
    Last Post: 08-13-2011, 09:53 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •