Results 1 to 8 of 8

Thread: Create Bubble Chart Automatically

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. 05-02-2011, 07:38 PM #1
    Excel Fox
    Excel Fox is offline
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    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
    Reply With Quote Reply With Quote
  2. 05-03-2011, 03:28 AM #2
    Rasm
    Rasm is offline
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    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
    Reply With Quote Reply With Quote
  3. 05-05-2011, 08:21 PM #3
    Admin
    Admin is offline
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    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
    Reply With Quote Reply With Quote
  4. 07-29-2012, 09:47 PM #4
    PC_BABE
    PC_BABE is offline
    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.
    Reply With Quote Reply With Quote
  5. 05-05-2011, 10:05 PM #5
    Excel Fox
    Excel Fox is offline
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Revised file with the above corrections

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316046#p316046
    https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
    https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316057#p316057
    https://eileenslounge.com/viewtopic.php?p=315915#p315915
    https://eileenslounge.com/viewtopic.php?p=316705#p316705
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=176255#p176255
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 07-27-2024 at 01:44 PM.
    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
    Reply With Quote Reply With Quote
  6. 07-30-2012, 02:06 AM #6
    PC_BABE
    PC_BABE is offline
    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?
    Reply With Quote Reply With Quote
  7. 08-06-2012, 04:30 PM #7
    Admin
    Admin is offline
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    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)
    Reply With Quote Reply With Quote
  8. 08-12-2012, 10:39 PM #8
    PC_BABE
    PC_BABE is offline
    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.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

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

Bookmarks

Posting Permissions

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

Forum Rules