none
Can't set chart legend fill to solid with VBA RRS feed

  • Question

  • G'day,

    I am trying to get VBA (in Excel 2010) to add a legend to an existing chart, and have the legend formatted to have a white solid fill with a solid black line border.  I can get the legend added, but I can't get the formatting correct. No matter what I try the formatting ends up as "no fill" and "no line".

    For the purpose of solving my problem, I have been working with code that will format an existing legend.  I recorded a macro to get the formatting I want and got the following code:

        With selection.Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorBackground1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Solid
        End With
        With selection.Format.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 0, 0)
        End With
        With selection.Format.Line
            .Visible = msoTrue
            .Weight = 0.25
        End With

    If I try running this code, I get a runtime error (the specified value is out of range) when it gets to the line:

            .ForeColor.ObjectThemeColor = msoThemeColorBackground1

    I have tried replacing that line with each of the following lines:

            .ForeColor.RGB = RGB(255, 255, 255)

            .ForeColor.SchemeColor = 9

    The code runs, but with no affect. 


    All I have been able to achieve is run the single line code:

        ActiveChart.Legend.Interior.ColorIndex = 2


    This sets the legend to a solid white fill, but I think it's on older version of code and I can't find equivalent to set the border as a solid black line.

    I have also tried changing the order around of the lines within the "With selection.Format.Fill" part with no difference.


    Cheers.

    Sunday, May 17, 2015 2:24 AM

Answers

  • Re:  chart legend colors

    ActiveChart.Legend.Interior.ColorIndex = 2
    ...is the same as...
    ActiveChart.Legend.Interior.Color = RGB(255, 255, 255)

    Also, this may work...
    ActiveChart.Legend.Border.Weight = xlThin      'xlMedium

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Craig1973 Monday, May 18, 2015 3:53 AM
    • Edited by James Cone Monday, October 31, 2016 1:48 PM
    Sunday, May 17, 2015 4:38 AM

All replies

  • Re:  chart legend colors

    ActiveChart.Legend.Interior.ColorIndex = 2
    ...is the same as...
    ActiveChart.Legend.Interior.Color = RGB(255, 255, 255)

    Also, this may work...
    ActiveChart.Legend.Border.Weight = xlThin      'xlMedium

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Craig1973 Monday, May 18, 2015 3:53 AM
    • Edited by James Cone Monday, October 31, 2016 1:48 PM
    Sunday, May 17, 2015 4:38 AM
  • At a glance there's nothing wrong with your code except we have no idea what your Selection is.

    After recording a macro it's always better to replace Selection and keywords that start with Active with an explicit reference to what you want to change. Eg, but warning this is air-code

    Dim cht As Chart
    Dim lgd As Legend
    Dim LgEty As LegendEntry
    Dim lgKey As LegendKey
    
    Set cht = ActiveChart
    Set lgd = cht.legend
    
    With lgd.Format.Fill
    ' type a dot and see the intellisense
    End With
    
    ' maybe format related items
    Set LgEty = lgd.LegendEntries(1)
    set lgKey = lgKey.LegendKey

    If you head modules 'Option Explicit' and fully declare all your variables chances are you'll find your errors before you even run any code, particularly if you do Debug/Compile

    The reason your colorindex = 2 code worked might be due to the fact that you didn't use Selection

    Sunday, May 17, 2015 11:03 AM
    Moderator
  • This question is also posted in Microsoft Office Excel forum.
    Sunday, May 17, 2015 2:49 PM
  • G'day Jim,

    Thanks for the rely.  With the ideas I got from you I used the following code that did just what I was after.

        Sub AddLegend(ChartName)

            ActiveSheet.ChartObjects(ChartName).Activate
            ActiveChart.HasLegend = True
            ActiveChart.Legend.Interior.Color = RGB(255, 255, 255)
            ActiveChart.Legend.Border.LineStyle = xlContinuous
            ActiveChart.Legend.Border.Color = RGB(0, 0, 0)
            ActiveChart.Legend.Border.Weight = xlHairline
            ActiveChart.Legend.Position = xlLegendPositionLeft
       
        End Sub

    Monday, May 18, 2015 3:56 AM
  • Thanks for the reply Peter.  I should have made it a bit clearer about the selection.  While I was experimenting with the code, I had the legend selected and then just ran the macro to see what it would do.

    I have learnt a lot from your post and the sample code you wrote.  I'm always trying to make my macros more professional.

    I now have the code doing what it needs to (as posted in another reply on this thread)

    Cheers.

    Monday, May 18, 2015 4:18 AM
  • Glad you've got it working but for reference you are essentially using legacy code for versions up to Excel 2003. That's fine if it does what you want and you should use it if you want to support these versions. However if you want to apply the extended range of formats available since 2007 you will need to use the Format.Fill and Format.Line objects, along the lines of the your recorded macro. Just be sure to fully reference the particular chart item along the lines I suggested.
    Monday, May 18, 2015 11:35 AM
    Moderator