Formatting Excel charts with VBA

edwardluce · March 16th, 2010, 08:34 PM

I cannot seem to get my VBA code to format a chart legend border, or even a chart area border. In fact the chart and the legend as well do not even appear to have a "borders" property. Has anyone run into this? When I try to record a macro to see how it's done there's no code in the recorded macro!

allenm · March 18th, 2010, 06:39 PM

Well, Not sure what exactly you're looking for but try this simple code sample with 5 columns and 7 rows (including heading row):

Code:

Public Sub MakeChart()

'Creates a table on current data 7 rows with heading and 5 columns
  Dim wsTarget As Worksheet, chToMake As ChartObject
  Set wsTarget = ActiveWorkbook.ActiveSheet
  Set chToMake = wsTarget.ChartObjects.Add(20, 100, 200, 200)
  With chToMake.Chart
    .ChartType = xlLine
    .HasTitle = True
    .ChartTitle.Caption = "Chart Title"
    .SetSourceData Source:=wsTarget.Range("A1:E7")
    .HasLegend = True
    With .Legend.Border
      .LineStyle = xlContinuous
      .Weight = xlMedium
    End With
    With .PlotArea.Border
      .LineStyle = xlContinuous
      .Weight = xlThin
    End With
  End With

End Sub

Hopefully this helps point in the right direction.

edwardluce · March 24th, 2010, 06:40 PM

Yes, it does help, but it does not completely solve my problem. "Borders" does not come up as an option on my IntelliSense when I type "Legend." This makes it difficult to ferret out the parent/child relationships between the various objects, properties, and methods.

A larger problem is to make one chart look like a similar one. I found a way to do it by copying the first chart and then using the PasteSpecial Format:=2 method on the second chart. But that's kind of clunky. It seems that if I had a pre-knowledge of all of the properties of the first chart (including those of all of it's objects), I could just set the second chart's properties to the same values. But the trouble is I haven't found a way to exhaustively debug.Print all of the properties of a chart exhaustively.

You can't write code like this:

For Each Property In ActiveChart
debug.Print Property
Next Property

All of the various chart objects like the legend, the chart area, the plot area, etc. have their own properties. Many are read only (cannot be set with VBA code).

So how can one discover all of the properties of any chart, including those of all of it's attendant objects? And once I do manage to discover them, how do I create a new chart with different data that looks very much like the first chart? Anybody?