Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Add Border To Chart Data Series Legend

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Add Border To Chart Data Series Legend

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


    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

    Comment


    • #3
      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.

      Comment


      • #4
        Re: Add Border To Chart Data Series Legend

        what is the best place for vba resource?
        Google

        Comment


        • #5
          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

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X