Chart - Legend Formatting

[jbeaucaire]

This question embarrassingly demonstrates my lack of time spent in the "Charts" arena.

On the sample workbook is an X/Y Scatter plot chart. What I would like to do by hand or by VBA:

1) Reduce the legend to only unique values
* Data1
* Data2
* Data3
etc...

2) Have all the dots of the same data type in the chart be matching in color.
Data1 = red
Data2 = green
Data3 = yellow
etc...


Cross-posted here with the workbook sample for convenience of the issue:
Chart - Legend Formatting

If this issue is resolved I will certainly and immediately update both threads with the solution and closure.

Thanks in advance.

 

[Worf]

Hello Jerry

Can you post a public link to the workbook? I cannot download from Excel Forum.

 

[jbeaucaire]

I've uploaded the file to my code site:
https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/

The file name is CalendarPOPUP.xls

 

[Worf]

Hi Jerry

I see you got a solution at Excel Forum. Would you mind posting it here?
Only members can view it there.

 

[jbeaucaire]

I'm still poking at this to understand how it causes the LEGEND to reduce the number of entries, but this wonder little macro from xlnitwit accomplishes both tasks quite neatly, merging the like items into a single colored item in a smaller legend.

Sub formatChart()

    Dim dic As Object
    Dim n As Long
    Dim theChart As Chart
    Dim theSeries As Series
    
    Set theChart = ActiveSheet.ChartObjects(1).Chart
    Set dic = CreateObject("Scripting.Dictionary")
    
    With theChart
        For n = .SeriesCollection.Count To 1 Step -1
            Set theSeries = .SeriesCollection(n)
            If dic.exists(theSeries.Name) Then
                .Legend.LegendEntries(n).Delete
            Else
                dic(theSeries.Name) = ActiveWorkbook.Colors(dic.Count + 1)
            End If
            With theSeries.Format
                With .Fill
                    .BackColor.RGB = dic(theSeries.Name)
                    .ForeColor.RGB = dic(theSeries.Name)
                End With
                With .Line
                    .BackColor.RGB = dic(theSeries.Name)
                    .ForeColor.RGB = dic(theSeries.Name)
                End With
            End With
        Next n
    End With
    Set dic = Nothing
End Sub

 

[Worf]

I added a few comments:

Sub formatChart()
Dim dic As Object, n%, theChart As Chart, theSeries As Series
Set theChart = ActiveSheet.ChartObjects(1).Chart
Set dic = CreateObject("Scripting.Dictionary")                        ' could also be a collection
With theChart
    For n = .SeriesCollection.Count To 1 Step -1                       ' all series
        Set theSeries = .SeriesCollection(n)                            ' one series
        If dic.exists(theSeries.Name) Then
            .Legend.LegendEntries(n).Delete                             ' exclude duplicate entry
        Else
            dic(theSeries.Name) = ActiveWorkbook.Colors(dic.Count + 1)  ' add color to dictionary
        End If
        With theSeries.Format                                           ' one series
            With .Fill
                .BackColor.RGB = dic(theSeries.Name)                    ' apply color
                .ForeColor.RGB = dic(theSeries.Name)
            End With
            With .Line
                .BackColor.RGB = dic(theSeries.Name)                    ' apply color
                .ForeColor.RGB = dic(theSeries.Name)
            End With
        End With
    Next
End With
Set dic = Nothing
End Sub