krishnak
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
.ErrorBars.Select
.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
myEB.Select
With myEB
.Format.Line.ForeColor.RGB = RGB(210, 0, 0)
.Format.Line.Weight = 3
End With
End Sub
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
.ErrorBars.Select
.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
myEB.Select
With myEB
.Format.Line.ForeColor.RGB = RGB(210, 0, 0)
.Format.Line.Weight = 3
End With
End Sub