Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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!

    Code:
    	'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.

    [vba]
    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
    [/vba]

    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,786

  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, 18:38
  2. Series Order in Legend, Multiple Chart Types
    By jmhans in forum EXCEL HELP
    Replies: 10
    Last Post: August 7th, 2006, 22: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