locked
How to change the ordering of legends in excel 2010 chart usng macro(VBA) RRS feed

  • Question

  • Hello,

    I want to change the order of legends in excel 2010 chart; I am able to change the ordering of legends but after changing the ordering of legends, series values are not changing accordingly due to this graph is displayed correctly.

    Please suggest the correct method to change the ordering of legends and accordingly change the series values in excel chart using macro (VBA) therefore graph can be displayed appropriately.

    Macro Code is as follows:

    LegendCount = chtChart.SeriesCollection.Count

      

       chtChart.SeriesCollection.NewSeries

       chtChart.SeriesCollection(9).name = chtChart.SeriesCollection(1).name

       chtChart.SeriesCollection(9).Values = chtChart.SeriesCollection(1).Values

       chtChart.SeriesCollection(9).Format.Fill.ForeColor.RGB = chtChart.SeriesCollection(1).Format.Fill.ForeColor.RGB

      

       chtChart.SeriesCollection.NewSeries

       chtChart.SeriesCollection(10).name = chtChart.SeriesCollection(5).name

       chtChart.SeriesCollection(10).Values = chtChart.SeriesCollection(5).Values

       chtChart.SeriesCollection(10).Format.Fill.ForeColor.RGB = chtChart.SeriesCollection(5).Format.Fill.ForeColor.RGB

      

       chtChart.SeriesCollection.NewSeries

       chtChart.SeriesCollection(11).name = chtChart.SeriesCollection(2).name

       chtChart.SeriesCollection(11).Values = chtChart.SeriesCollection(2).Values

       chtChart.SeriesCollection(11).Format.Fill.ForeColor.RGB = chtChart.SeriesCollection(2).Format.Fill.ForeColor.RGB

      

       chtChart.SeriesCollection.NewSeries

       chtChart.SeriesCollection(12).name = chtChart.SeriesCollection(6).name

       chtChart.SeriesCollection(12).Values = chtChart.SeriesCollection(6).Values

       chtChart.SeriesCollection(12).Format.Fill.ForeColor.RGB = chtChart.SeriesCollection(6).Format.Fill.ForeColor.RGB

      

       chtChart.SeriesCollection.NewSeries

       chtChart.SeriesCollection(13).name = chtChart.SeriesCollection(3).name

       chtChart.SeriesCollection(13).Values = chtChart.SeriesCollection(3).Values

       chtChart.SeriesCollection(13).Format.Fill.ForeColor.RGB = chtChart.SeriesCollection(3).Format.Fill.ForeColor.RGB

      

       chtChart.SeriesCollection.NewSeries

       chtChart.SeriesCollection(14).name = chtChart.SeriesCollection(7).name

       chtChart.SeriesCollection(14).Values = chtChart.SeriesCollection(7).Values

       chtChart.SeriesCollection(14).Format.Fill.ForeColor.RGB = chtChart.SeriesCollection(7).Format.Fill.ForeColor.RGB

      

       chtChart.SeriesCollection.NewSeries

       chtChart.SeriesCollection(15).name = chtChart.SeriesCollection(4).name

       chtChart.SeriesCollection(15).Values = chtChart.SeriesCollection(4).Values

       chtChart.SeriesCollection(15).Format.Fill.ForeColor.RGB = chtChart.SeriesCollection(4).Format.Fill.ForeColor.RGB

      

       chtChart.SeriesCollection.NewSeries

       chtChart.SeriesCollection(16).name = chtChart.SeriesCollection(8).name

       chtChart.SeriesCollection(16).Values = chtChart.SeriesCollection(8).Values

       chtChart.SeriesCollection(16).Format.Fill.ForeColor.RGB = chtChart.SeriesCollection(8).Format.Fill.ForeColor.RGB

       'Deletion of old legends

       For i = 1 To LegendCount

            chtChart.Legend.LegendEntries(1).Delete

       Next i

    Thank You

    Tuesday, February 24, 2015 5:17 PM

Answers

  • re:  ChartLegend

    The line "chtChart.SeriesCollection.NewSeries" does nothing.

    After legend entries have been deleted, the only way to restore them is to remove and recreate the legend that contained them by setting the HasLegend property for the chart to False and then back to True...
      chtChart.HasLegend = False
      chtChart.HasLegend = True
    (the font in the new legend may need adusting)
    '---
    It has been a very long time since I have created charts with VBA.
    My Excel help file says to change the values used by a Chart Series you have to specify a range or provide an array of values (not both)
    So this seems to work...
    Dim X as Variant
    X = chtChart.SeriesCollection(3).Values
    chtChart.SeriesCollection(1).Values = X

    '---

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

    • Marked as answer by Programmer1982 Thursday, February 26, 2015 6:43 AM
    • Edited by James Cone Monday, October 31, 2016 6:51 PM
    Tuesday, February 24, 2015 7:59 PM

All replies

  • re:  ChartLegend

    The line "chtChart.SeriesCollection.NewSeries" does nothing.

    After legend entries have been deleted, the only way to restore them is to remove and recreate the legend that contained them by setting the HasLegend property for the chart to False and then back to True...
      chtChart.HasLegend = False
      chtChart.HasLegend = True
    (the font in the new legend may need adusting)
    '---
    It has been a very long time since I have created charts with VBA.
    My Excel help file says to change the values used by a Chart Series you have to specify a range or provide an array of values (not both)
    So this seems to work...
    Dim X as Variant
    X = chtChart.SeriesCollection(3).Values
    chtChart.SeriesCollection(1).Values = X

    '---

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

    • Marked as answer by Programmer1982 Thursday, February 26, 2015 6:43 AM
    • Edited by James Cone Monday, October 31, 2016 6:51 PM
    Tuesday, February 24, 2015 7:59 PM
  • Hi Jim,

    Thanks!!!

    Thursday, February 26, 2015 6:44 AM
  • Hi,

    For this requirement, I suggest that you could change PlotOrder property directly.

    For example:

    chtChart.SeriesCollection(9).PlotOrder=1

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 26, 2015 6:45 AM