Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2010
    Location
    Hilton Head SC
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a Excel 2007 worksheet page with five charts, each with the identical legend. To save space in each chart area, I would like to remove the legend from each chart (no problem doing that) and paste it once, elsewhere on the page outside the charts. Can anyone suggest a way?

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Hi Susan
    I can't think of a way to extract the legend outside of the chart.
    So this is the way I'd do it:
    • copy the chart - as a picture (you can find this option under the Paste Icon oddly enough).
    • Paste the picture of the chart into the worksheet
    • Use the picture format ribbon and crop the picture of the chart down to the legend only.
    The drawback of this method is that the picture is static - not linked to the charts. So if you edit the colours or labels on the charts, you'll need to recopy/paste the legend.

    HTH
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I'd just leave the legend on the first chart, I think.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    790
    Thanks
    38
    Thanked 43 Times in 33 Posts
    Consider using something like Gadwin Print Screen (free) to capture a rectangle screen image that includes only the legend; then paste it into excel wherever you want it, as an image.

    That way your charts remain "alive".

    Get Gadwin Print Screen at: http://www.gadwin.com/printscreen/
    (My Setup: Custom built: 4.00GHz Intel Core i7-6700K CPU; MSI Z170A Gaming Carbon Motherboard (Military Class III); Win 10 Pro (64 bit)-(UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; 2 X GeForceGTX 1070 8GB Graphics Card (SLI); Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2017 Premium, Norton Internet Security, VMWare Workstation12 Pro). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Thinking about it, you could also create a 6th chart, showing the legend, clear the axes and any titles, then shrink the plot area as small as possible and just leave the legend showing and use that as your legend for all the charts.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. The Following User Says Thank You to rory For This Useful Post:

    GregTheSquarePeg (2013-07-02)

  7. #6
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    790
    Thanks
    38
    Thanked 43 Times in 33 Posts
    @Rory: Neat!
    (My Setup: Custom built: 4.00GHz Intel Core i7-6700K CPU; MSI Z170A Gaming Carbon Motherboard (Military Class III); Win 10 Pro (64 bit)-(UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; 2 X GeForceGTX 1070 8GB Graphics Card (SLI); Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2017 Premium, Norton Internet Security, VMWare Workstation12 Pro). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

  8. #7
    New Lounger
    Join Date
    Jul 2013
    Posts
    1
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Rory - this was a great idea and worked a charm.

  9. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts

    Using VB

    Greg,

    Here is some code that will automate the process. Space limiting, this example uses only three charts but can be any amount you prefer. Since they are all the same, the code will read the legend from only one of the charts extracting the series colors and names. I have provided for up to 5 series on the chart (only 2 shown in sample). It will then create a text box and build the legend with the names and colors from the chart. Finally, the legends from the charts are removed.

    HTH,
    Maud

    Series1.png Series3.png

    Code:
    Option Base 1
    
    Public Sub NewLegend()
    On Error Resume Next
    'DECLARE VARIABLES
    Dim GraphColor(5) As Long  'ARRAY- LINE COLOR
    Dim SeriesName(5) As String  'ARRAY- NAME OF SERIES
    Dim SeriesSize(5) As Integer  'ARRAY-  LENGTH OF SERIES NAME
    Dim ChartNum As Integer  'NUMBER OF CHARTS ON WORKSHEET
    Dim I As Integer  'COUNTER
    Dim H As Integer  'COUNTER
    Dim Legend  'CONCATENATION OF SERIES NAMES
    Legend = ""
    '-------------------------------------------------------------------------
    'GET SERIES COLOR, NAME, AND NAME LENGTH ASSIGN TO ARRAY VARIABLED
    ActiveSheet.ChartObjects(1).Activate
    With ActiveChart
    I = 1
    For Each Series In .SeriesCollection
        GraphColor(I) = .SeriesCollection(I).Format.Line.ForeColor.RGB
        SeriesName(I) = .SeriesCollection(I).Name
        SeriesSize(I) = Len(SeriesName(I))
        I = I + 1
    Next Series
    '--------------------------------------------------------------------------
    'BUILD NEW LEGEND- INSERT TEXTBOX, CONCATENATE SERIES NAMES
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 220, 40, 120, _
            26 * (I - 1)).Select
    For H = 1 To I - 1
        Legend = Legend & "_____ " & SeriesName(H) & Chr(13)
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Legend
    Next H
    '--------------------------------------------------------------------------
    'FORMAT EACH SERIES BAR IN LEGEND
    Start = 1
    For H = 1 To I - 1
        'GET RED, GREEN, BLUE VALUES OF COLOR FOR EACH SERIES
        R = Red(GraphColor(H))
        G = Green(GraphColor(H))
        B = Blue(GraphColor(H))
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(Start, 5).Font
        .BaselineOffset = 0.3  'VERTICALLY CENTER BAR
        .Bold = True  'MAKE BAR THICKER
        .Fill.ForeColor.RGB = RGB(R, G, B)  'CHANGE BAR TO SERIES COLOR
        .Size = 18  'MAKE BAR EVEN THICKER
    End With
    Start = Start + SeriesSize(H) + 7 'SET STARTING POINT FOR FORMATTING
    Next H
    '---------------------------------------------------------------------------
    'REMOVE LEGENDS FROM EACH CHART
    ChartNum = ActiveSheet.ChartObjects.Count
    For I = 1 To ChartNum
        ActiveSheet.ChartObjects(I).Chart.Legend.Delete
    Next I
    
    End With
    End Sub
    
    Public Function Red(ByVal Value As Long)
    Red = Value Mod 256
    End Function
    
    Public Function Blue(ByVal Value As Long)
    Blue = Int(Value / 256 / 256) Mod 256
    End Function
    
    Public Function Green(ByVal Value As Long)
    Green = Int(Value / 256) Mod 256
    End Function
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •