Dymamically Change Marker Size and Background Color of XY Scatter Chart

[MJU007]

Hi all, I have a problem that I've literally lost sleep over

I have a XY Scatter with Straight Lines and Markers chart. This chart shows a stakeholder relationship between the different team members.

For each member, we want to define a marker colour and size. The members, sizes and colours will be specific to each client the report is presented to. In addition the number of members will range from 5 to 15.

I know how to do this with a standard line chart. However, the same concept doesn't seem to work with the XY Scatter.

Can any one help?

Thanking you in advance.
M.

 

[Worf]

Hi and welcome to the Board

See if this helps:

Sub FormatScatter()
Dim s As Series, ch As Chart, r, g, b, i%, j%
r = Array(200, 150, 100, 50, 180)                           ' red components
g = Array(40, 80, 140, 180, 240)                            ' green components
b = Array(100, 150, 200, 30, 190)                           ' blue components
Set ch = ActiveSheet.ChartObjects("Chart5").Chart
ch.ChartType = xlXYScatterLines
For i = 0 To UBound(r)                                      ' loop series
    For j = 1 To ch.SeriesCollection(i + 1).Points.Count    ' loop points
        Set s = ch.SeriesCollection(i + 1)
        s.Points(j).Format.Fill.ForeColor.RGB = RGB(r(i), g(i), b(i))
        s.Points(j).MarkerSize = 7 + 2 * i
    Next
Next
End Sub

 

[MJU007]

Thanks for replying. As I am still trying to come up to speed with the VB please forgive my ignorance, but I need to define the colour and size in the spreadsheet against the stakeholder/member. I can't determine from this script where it gets its size and color instructions from.

regards
M.

 

[Worf]

At the moment color and size are set entirely by code. Would you like to get this information from a sheet range?

Sub FormatScatter()
Dim s As Series, ch As Chart, r, g, b, i%, j%
r = Array(200, 150, 100, 50, 80)                           ' red components
g = Array(40, 80, 140, 180, 40)                            ' green components
b = Array(100, 150, 200, 30, 190)                           ' blue components
Set ch = ActiveSheet.ChartObjects("Chart5").Chart           ' your chart name here
ch.ChartType = xlXYScatterLines
For i = 0 To UBound(r)                                      ' loop series
    For j = 1 To ch.SeriesCollection(i + 1).Points.Count    ' loop points
        Set s = ch.SeriesCollection(i + 1)
        s.Points(j).Format.Fill.ForeColor.RGB = RGB(r(i), g(i), b(i))   ' set point colour
        s.Points(j).MarkerSize = 7 + 2 * i                              ' set marker size
    Next
Next
End Sub

 

[MJU007]

Yes. In my original post I uploaded the excel worksheet with an example of the chart and the information it used to generate it. (I'd upload it against this reply, but I can't find how I would do it.)

On the 2nd tab, we enter the names of each person in the team, allocate a colour, size and then specify the relationship strength to the other members.

This then presents us with our stakeholder map (first tab)

I have managed to get everything working except for the dynamic colour and size that was entered against the person in the 2nd sheet.

Is this something that is even possible?

regards
M.

 

[MJU007]

Whoops.. looks like I was dreaming about uploading it... it must be the lack of sleep over this

 

[Worf]

Is this something that is even possible?

I think so.

You can upload the workbook to a sharing site like Drop Box and paste the link here.
I can offer a quicker solution working with the actual file.

 

[MJU007]

Hi, I've uploaded the file to dropbox. The link is below.

https://www.dropbox.com/s/n8n5jpp2w8a5601/DASHBOARD_WIP_Cut_Down.xlsm?dl=0

Thanks so much for your help on this.
M.

 

[Worf]

Hi

I’m on Excel 2007 today, so I can’t use the slicer. What version are you using?
When I dismantled the chart, the code below worked, see the picture. However it did nothing with the original chart.
Anyway, see what happens with your Excel version and post back.

Sub FormatMap()
Dim sh As Worksheet, ch As Chart, csh As Worksheet, s As Series, i%, c&, tl As Range
Set csh = Sheets("RELATIONSHIP MAP")
Set ch = csh.ChartObjects("SHMap2").Chart
Set sh = Sheets("2. DATA GUIDING")
Set s = ch.SeriesCollection("Stakeholder Dots")
Set tl = sh.[a30]                                           ' top left cell of data table
For i = 1 To 20                                             ' all possible members
    If Len(tl.Offset(i)) Then                               ' member exists
        s.Points(i).MarkerSize = tl.Offset(i, 2)            ' change marker size
        c = tl.Offset(i, 3).Interior.Color                  ' get cell color
        s.Points(i).Format.Fill.ForeColor.RGB = RGB(c Mod 256, c \ 256 Mod 256, c \ 65536 Mod 256)
    End If
Next
End Sub

 

[MJU007]

Hi, I use both 2013 and 2016. The code works a treat!!! You are an absolute legend

Thanks so very much
M.