Alex St-Pierre said:
Hi,
I'm using a MS Graph and every time I add a new row, the bar and the legend
have a borders. Is there a way to remove it (or modify the color).
Thank you!
Alex
It took me a while to figure this one out...
The online help says that the constant for no border is "xlLineStyleNone",
but the constant for a regular bodrer is "xlContinuous"... After a while it
occured to me that there was a mistake in the online help.. which is
surprising... Not!
In any case, here is some code that creates and then manipulates a chart:
Sub Test()
'You need to set a reference to "Microsoft Graph 11.0 Object Library" in
'Tools > References (VBA Editor menu bar).
'Declare some variables:
Dim
LE As Word.OLEFormat
Dim diag As Graph.Chart
Dim rngInsertGraph As Range
'Inserting graph at current selection
Set rngInsertGraph = Selection.Range
rngInsertGraph.InsertParagraphBefore
rngInsertGraph.Collapse wdCollapseStart
rngInsertGraph.InlineShapes.AddOLEObject ClassType:="MSGraph.Chart.8", _
FileName:="", LinkToFile:=False, DisplayAsIcon:=False
Set
LE = rngInsertGraph.Paragraphs(1).Range.InlineShapes(1).OLEFormat
LE.DoVerb wdOLEVerbShow
Set diag =
LE.Object
'Manipulate the object like this:
With diag
With .Application.DataSheet
'By default, a newly inserted chart is 5x4.
'Assuming row one and column one contain headers _
which means that .Cells(1,1) is empty
.Cells(1, 2).Value = "2004"
.Cells(1, 3).Value = "2005"
.Cells(1, 4).Value = "2006"
.Cells(2, 1).Value = "Honda"
.Cells(3, 1).Value = "Toyota"
.Cells(4, 1).Value = "Mazda"
.Cells(2, 2).Value = 98
.Cells(2, 3).Value = 105
.Cells(2, 4).Value = 125
.Cells(3, 2).Value = 65
.Cells(3, 3).Value = 85
.Cells(3, 4).Value = 79
.Cells(4, 2).Value = 105
.Cells(4, 3).Value = 93
.Cells(4, 4).Value = 101
'Other possible manipulations:
.Columns(5).ClearContents
' .Rows(3).ClearContents
End With
'Sample code to manipulate the chart istself
.ChartArea.Border.LineStyle = xlDash
.HasLegend = True
.Legend.Font.ColorIndex = 5
.Legend.Border.LineStyle = xlNone
.Application.PlotBy = xlColumns
.Width = CentimetersToPoints(25)
.PlotArea.Width = CentimetersToPoints(8)
.Axes(xlCategory, xlPrimary).TickLabelSpacing = 10
.Axes(xlCategory, xlPrimary).TickLabels.Orientation = 90
.BarShape = xlCylinder
.SeriesCollection(1).HasDataLabels = True
.SeriesCollection(2).HasDataLabels = True
.SeriesCollection(3).HasDataLabels = True
.SeriesCollection(1).Border.LineStyle = xlDash
.SeriesCollection(2).Border.LineStyle = xlNone
.SeriesCollection(3).Border.LineStyle = xlContinuous
.Walls.Interior.Color = RGB(100, 255, 0)
End With
'And do not forget to destroy the objects:
'Deactivate the graph object
diag.Application.Quit
'Clear objects
Set diag = Nothing
Set
LE = Nothing
SendKeys "{ESC}"
End Sub
