Bubble charts and colours

[plant007]

Hi Everybody

I hope you can help me - I have been searching for the answer and got so far but cant seem to get over the final hurdle

I have worked out how to create a bubble chart and pieced together a macro from others work, with the aim of making the bubbles coloured based on a column of values (Green, Amber/Green,Amber,Amber/Red,Red). My code is below but it doesn't address;

1) two colours required on one bubble (Amber /red and amber/green) - comes up grey at present
2) although the bubbles data has been sorted descending to avoid hidden bubbles, I really need to have only the edge of the bubble coloured and the interior transparent
3) I cant seem to add the labels of the entry's correctly (in this case, project names), and when you hover over the bubble, it just states the series name

Any help would be greatly appreciated
I am leaving work now but will pick and reply to any replies tomorrow morning
Thanks
Andy

Sub ColorPoints()
Dim i As Integer
Dim myRange As Range
Dim iColor As Integer
Set myRange = ActiveSheet.Range("M2:M52")
For i = 1 To myRange.Rows.Count
Select Case WorksheetFunction.Index(myRange, i).Value
Case "Green"
iColor = 10 ' green
Case "Amber/Green"
iColor = 10 / 44
Case "Amber"
iColor = 44 ' orange
Case "Amber/Red"
iColor = 44 / 3
Case "Red"
iColor = 3
End Select
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1). _
Points(i).Interior.ColorIndex = iColor




Next
End Sub

 

[Eric W]

When you set your color like this:

iColor = 44 / 3

what is happening is that VBA thinks you are dividing, and sets iColor to 14.666666666. And since iColor is defined as an integer, it truncates it to 14, which may be the gray color you see. If you want to change the bubble color to have 2 shades (Amber and Red), there are a few options. I don't see any option for left half = Amber, right half = Red. But there is a gradient option where you pick 2 colors and the colors blend into each other. But it comes out banded somehow. One option I found that looks promising, is that you can set the color of the bubble to Amber, then set the color of the border line to Red, and set the width of the border line to something very thick. This way you'll have 2 colors and they'll have well-defined borders, but they'll be inside/outside instead of left/right.

Now to actually code for that. I'd recommend you go to Excel, and select one of the bubbles. Then right click on it, select Format Data Point, then play with the Fill, Border Color, and Border Styles options until you find something you like. Once you do, go to the Developer tab, click Record Macro, and repeat the steps you did to format the data point. Then click Stop Recording. Then view the generated code in the VBA editor and you should be able to adapt it so that it handles your requirements within your macro.

Let me know if you have any questions.

 

[plant007]

Hi Eric

Many many thanks for your help -I tried adjusting manually and recording but no success - it doesn't seem to record on macro. I also need to remove the fills on the circles as they overlap so much so I unfortunately need to just have the rims and keep the inners transparent

I have found some code for the labels and will look at that

Any help on this would be gratefully received
Thanks
Andy

 

[Eric W]

The macro recorder does record the changes. Here's a snippet of code I got when I experimented:

    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(4).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With

Since I did lots of changes, I had pages of code like this. That's why I suggested figuring out what you want before starting the recorder. If you can't find where the recorder put the code, look for a new Module in the VBA editor.

If you're having trouble with the size of the bubbles, you can go to the Format Data Series option, then Scale bubble size to: and select a smaller number. Or possibly even select a different type of chart.

I'm not sure where I can help at this point. First you need to design your chart, allowing for the fact that you plan on modifying it via a macro. If you do have trouble with the recorder, or you need help figuring out how to adapt the recorder code to your needs, let me know.

 

[plant007]

Thanks for this Eric. Given time constraints, I have already had to go with non transparent bubbles and as per your idea, prioritized which bubbles I added a coloured rim to ie the red ones. For me, this is a really important graph type so I will look at your macro code next week but in the meantime, many thanks for this. Its already cleared something up in my head that the rim is not added but is part of the interior (given your code above), but when you ask for the interior to be transparent, the rim stays as the original colour

Many thanks again
Andy

 

[Eric W]

You're welcome.