View Full Version : [SOLVED] Excel 2007 charts - ErrorBars

02-28-2011, 01:25 PM
Hi All,

I have to create a fixed value benchmark for some charts. I am able to create the same with ErrorBars.
But I am not able to change the color or the line weight of the ErrorBar by vba. However I am able to do it manually.

The code is posted below. The problem is in the last part of the code where I want to format the ErrorBars.
Please note that this is the code for the ErrorBar only. A simple line chart is prepared before running this code.
I'll appreciate any help.

'Insert benchmark values in a chart.
Sub InsertBenchmark()
Dim cht As Chart, myEB As ErrorBars
Dim objAxis As Axis, mySeries As Series
Dim refSeries As Series

Set cht = ActiveSheet.ChartObjects("RefChart").Chart
With cht.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("P2")
.Values = ActiveSheet.Range("P3") ' Y Axis values
.XValues = ActiveSheet.Range("O3") ' X Axis values
End With
' Set the NewSeries to the variable.
Set mySeries = cht.SeriesCollection("NAES")
'Set the primary series also to a variable.
Set refSeries = cht.SeriesCollection("Value")
With mySeries
'Set the chart type
.ChartType = xlXYScatter
'Assign the secondary axes to the new series.
.AxisGroup = xlSecondary
End With
'Set the scales for the secondary axes of the chart.
Set objAxis = cht.Axes(xlCategory, xlSecondary)
With objAxis
.MaximumScale = 1
.MinimumScale = 0
.TickLabelPosition = xlTickLabelPositionNone
End With
Set objAxis = cht.Axes(xlValue, xlSecondary)
With objAxis
.MaximumScale = cht.Axes(xlValue, xlPrimary).MaximumScale
.MinimumScale = cht.Axes(xlValue, xlPrimary).MinimumScale
.TickLabelPosition = xlTickLabelPositionNone
End With
'Set the error bars for the series
With mySeries
.HasErrorBars = True
.ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlFixedValue, Amount:=0
' .ErrorBars.Select
' .ErrorBars.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
' .ErrorBars.Format.Line.Weight = 3
End With

Set myEB = mySeries.ErrorBars
With myEB
.Format.Line.ForeColor.RGB = RGB(210, 0, 0)
.Format.Line.Weight = 3
End With
End Sub

03-01-2011, 12:38 PM
If we use the Border property of the ErrorBars (instead of Format), the ErrorBar is formatted.
The last part of the code has to be changed as given below:

.ErrorBars.Border.Color = vbRed ' Red color
.ErrorBars.Border.Weight = 3

Then the ErrorBar is formatted with red color and the line weight is 3.