none
Using VBA to assign legend colors RRS feed

  • Question

  • I have several charts that have specific colors or patterns in the legends. In particular, each business line has its own assigned RGB values. I have 15 possible business lines.   In code, I am trying to assign these RGB values. I can do it this way:

    ' AssColor4 Macro
    '
        ActiveChart.Legend.Select
     
       '----------------------------------------------------------------------------------
       'BSA/AML
       ActiveChart.SeriesCollection(1).Select
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(51, 204, 51)
            .Transparency = 0
            .Solid
        End With
        With Selection.Format.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 0, 0)
            .Transparency = 0
        End With
        
        With Selection.Format.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 0, 0)
            .Transparency = 0
        End With
       ' Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
        '________________________________________________________________________________________________
        'Commercial
        ActiveChart.Legend.Select
        ActiveChart.SeriesCollection(2).Select
        'ActiveChart.Legend.LegendEntries(10).Select
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 102)
            .Transparency = 0
            .Solid
        End With
        With Selection.Format.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 0, 0)
            .Transparency = 0
        End With
        ActiveChart.SeriesCollection(2).DataLabels.Select
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorBackground1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
        End With
        With Selection.Format.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 0, 0)
            .Transparency = 0
        End With
        Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue

    I can repeat this 15 times, but there should be a way that I should be able to iterate through the name property, but I can't seem to get this to work.  When I try:

    Dim i as integer

    i=1

    If ActiveChart.SeriesCollection(i).Name = "BSA/AML" Then
    Debug.Print ActiveChart.SeriesCollection(i).Name

        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(51, 204, 51)
            .Transparency = 0
            .Solid
        End With
    ElseIf ActiveChart.SeriesCollection(i).Name = "Commercial" Then
        ActiveChart.SeriesCollection(i).Select
        With Selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 102)
            .Transparency = 0
            .Solid
        End With........etc. etc.....

    I can't get the assignment to work.  Any ideas?


    Dean J. Waring

    Monday, January 30, 2017 8:46 PM

Answers

  • Oops - sorry. just one. I started w/ oThisSeries & later changed to oSeries.

    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com

    • Marked as answer by DeanJW2006 Tuesday, January 31, 2017 2:27 PM
    Tuesday, January 31, 2017 1:52 PM

All replies

  • Hi Dean -

    I would lean toward trying a For/Each construct because the SeriesCollection returns Series Objects and they have a Name property (that is supposed to link to the associated LegendEntry in the LegendEntries collection of the Legend via the LegendKey - but I don't see a Name property for LegendLey or LegendEntries).

    So, to Access the Series Formats (Not the Legend Entries), it would look something like:

        Dim oSeries As Excel.Series
        Dim lngForeColor As Long        ' This is an example for ForeColor'
        'you can use this pattern for other Series Properties.
        'I just prefer to use a Select/Case for Property assignments
        '   & then make the assignment at the end so I can read what I wrote.
        '   You can also seperate a problem with a Property assignment from'
        '   what the actual Property value should be.
        ''
        On Error GoTo err_SetLegends
        For Each oSeries In ActiveChart.SeriesCollection
            With oSeries
                Select Case .Name
                    Case "Commercial"
                        lngForeColor = RGB(255, 0, 102)
                    Case "BSA/AML"
                        lngForeColor = RGB(51, 204, 51)
                    Case "xyz"
                    'And So On'
                End Select
                oSeries.Format.Fill.ForeColor.RGB = lngForeColor
            End With
        Next oSeries
    exit_SetLegends:
        Exit Sub
    err_SetLegends:
        MsgBox "Error " & Err.Number & "Occurred in SetLegends:" & vbCrLf & Err.Description
        Resume Next
    Your code also refers to "With Selection.Format.Fill", which probably is changing the same Series Object with the different formats.


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com



    Monday, January 30, 2017 11:00 PM
  • Thank you for your help. I will work with this and be back to you....

    Dean J. Waring

    Monday, January 30, 2017 11:06 PM
  • Do we have two variables--oThisSeries and oseries--or just one?  Getting a variable not defined error.

    Dean J. Waring

    Tuesday, January 31, 2017 1:17 PM
  • Oops - sorry. just one. I started w/ oThisSeries & later changed to oSeries.

    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com

    • Marked as answer by DeanJW2006 Tuesday, January 31, 2017 2:27 PM
    Tuesday, January 31, 2017 1:52 PM
  • Thanks...that worked great...now I'll see if I can get the lines boxes and fill done....

    Dean J. Waring

    Tuesday, January 31, 2017 2:27 PM