Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Add Border To Chart Data Series Legend

  1. #1
    Join Date
    15th March 2008
    Posts
    11

    Add Border To Chart Data Series Legend

    Hi all,

    to begin with, I need to be specific about where the border needs to be placed. For an excel bar graph legend, it displays a colored box with a text title corresponding the data series it represents. I want to put a border around the colored box. I think this should be possible, since I was able to do it with Excel.

    My code looks similar to the ones below. I tried many different combinations to try to access the colored box, but none gave the results I want (ex .Legend.LegendEntries.border = 1, .Legend.Format.border =1, etc).

    Please help me out, I am a novice when it comes to writing macros for MS Excel. Thanks in advance!

    VB:
     'Create chart object
    Set oChartObj = .ChartObjects.Add( _ 
    Left:=400, _ 
    Top:=100, _ 
    Width:=500, _ 
    Height:=400) 
     
     'Now build the Chart within the ChartObject
    With oChartObj.Chart 
        .ChartType = xlXYScatterSmooth 'define chart type
         
         'Add each series
        For i = 2 To rHeaders.Count 
            With .SeriesCollection.NewSeries 
                .Values = rData.Columns(i) 
                .XValues = rData.Columns(1) 
                .Name = rHeaders.Cells(i) 
            End With 
        Next i 
         'Format Legend
        .HasLegend = True 
        With .Legend 
            .Position = xlRight 
            With .Border 
                .Color = vbBlue 
                .LineWeight = 1 
                .LineStyle = xlContinuous 
                .Transparency = 0 
            End With 
            With .Fill 
                .Visible = True 
                .ForeColor.RGB = RGB(150, 200, 255) 
                .Transparency = 0.5 
            End With 
            With .Font 
                .Name = "Arial" 
                .Size = 10 
                .ColorIndex = 5 
            End With 
        End With 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Add Border To Chart Data Series Legend

    Something like the following will allow you to change the appearance of a legend key.

    VB:
    With ActiveChart 
        With .Legend 
            With .LegendEntries(1).LegendKey 
                With .Border 
                    .Weight = xlHairline 
                    .LineStyle = xlNone 
                End With 
                .MarkerBackgroundColorIndex = 3 
                .MarkerForegroundColorIndex = 1 
                .MarkerStyle = xlDiamond 
                .Smooth = False 
                .MarkerSize = 35 
                .Shadow = False 
            End With 
        End With 
    End With 
    
    

    Cheers
    Andy


  3. #3
    Join Date
    15th March 2008
    Posts
    11

    Re: Add Border To Chart Data Series Legend

    thanks Andy,

    I will try that out. Just curious, and this question goes out to others as well, what is the best place for vba resource?

    I find that for VBA a lot of properties are not documented, not even on the MSDN website.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  5. #5
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: Add Border To Chart Data Series Legend

    These are the resources I normally use to discover things.

    Intellisense
    Object Browser
    Google
    Here at Oz
    Other Forums
    Newsgroups
    Books
    Macro recorder

    Cheers
    Andy


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Charts, Series Name, Legend Key, Data Label
    By mwilliams1220 in forum EXCEL HELP
    Replies: 3
    Last Post: June 1st, 2007, 17:38
  2. Series Order in Legend, Multiple Chart Types
    By jmhans in forum EXCEL HELP
    Replies: 10
    Last Post: August 7th, 2006, 21:24

Bookmarks

Posting Permissions

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