DashBoarder
New Member
- Joined
- Sep 9, 2014
- Messages
- 9
Excel 2010, 64-bit (14.0.7015.1000)
Win8 Pro
I'm using error bars as a means of creating "target bars" for a bar-chart. Further, I'm trying to use VBA code to both create and modify these target bars. Although I seem to have little problem creating them, I'm having issues being able to edit these bars once they're created. Below is a little test program that creates a chart, creates the main chart series, then creates the target bar (error bar) series, and then tries to edit the target bars to change their color and line weight. The section of code at the bottom that attempts to edit the target bars is not working properly since it does not set the X-directional error bar's color & line weight...
-------------------------------------------------
Sub TargetBarTest()
'Create the chart and the main and target bar series...
With ActiveSheet.ChartObjects.Add(******=20, *******=300, *****=20, ********=200)
With .Chart
.ChartType = xlColumnStacked100
.Parent.Name = "Chart 1"
.HasTitle = False
.HasLegend = False
With .SeriesCollection.NewSeries
.Name = "Series 1"
.Values = "={2,5,3}"
.ChartType = xlColumnClustered
End With
With .SeriesCollection.NewSeries
.Name = "Target Bar"
.Values = "={2.2,4.5,2.8}"
.ChartType = xlXYScatter
.HasErrorBars = True
.ErrorBars.EndStyle = xlNoCap
.ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlFixedValue, Amount:=0#
.ErrorBar Direction:=xlX, Include:=xlBoth, Type:=xlFixedValue, Amount:=0.2
.MarkerStyle = xlMarkerStyleNone
End With
End With
End With
ActiveSheet.ChartObjects("Chart 1").Activate
'Here's where I attempt to set the color & line weight of the target bars (i.e. error bars)...
With ActiveChart.SeriesCollection("Target Bar")
.ErrorBars.Format.Line.Visible = msoTrue
.ErrorBars.Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'i.e. RED
.ErrorBars.Format.Line.Weight = 2#
End With
End Sub
---------------------------------------------------
PLEASE NOTE that the target bar editing code DOES indeed have an effect - but not on the error bars in the X-direction! For example, if you change the "Amount:=0" setting above for the error bar Y-direction then you'll see that the Y-direction error bar turns red and has it's line weight set to 2. But that's not what I want; I want to modify the X-directional error bars.
If this is not clear, then please feel free to ask for more info.
Thanks!
Win8 Pro
I'm using error bars as a means of creating "target bars" for a bar-chart. Further, I'm trying to use VBA code to both create and modify these target bars. Although I seem to have little problem creating them, I'm having issues being able to edit these bars once they're created. Below is a little test program that creates a chart, creates the main chart series, then creates the target bar (error bar) series, and then tries to edit the target bars to change their color and line weight. The section of code at the bottom that attempts to edit the target bars is not working properly since it does not set the X-directional error bar's color & line weight...
-------------------------------------------------
Sub TargetBarTest()
'Create the chart and the main and target bar series...
With ActiveSheet.ChartObjects.Add(******=20, *******=300, *****=20, ********=200)
With .Chart
.ChartType = xlColumnStacked100
.Parent.Name = "Chart 1"
.HasTitle = False
.HasLegend = False
With .SeriesCollection.NewSeries
.Name = "Series 1"
.Values = "={2,5,3}"
.ChartType = xlColumnClustered
End With
With .SeriesCollection.NewSeries
.Name = "Target Bar"
.Values = "={2.2,4.5,2.8}"
.ChartType = xlXYScatter
.HasErrorBars = True
.ErrorBars.EndStyle = xlNoCap
.ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlFixedValue, Amount:=0#
.ErrorBar Direction:=xlX, Include:=xlBoth, Type:=xlFixedValue, Amount:=0.2
.MarkerStyle = xlMarkerStyleNone
End With
End With
End With
ActiveSheet.ChartObjects("Chart 1").Activate
'Here's where I attempt to set the color & line weight of the target bars (i.e. error bars)...
With ActiveChart.SeriesCollection("Target Bar")
.ErrorBars.Format.Line.Visible = msoTrue
.ErrorBars.Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'i.e. RED
.ErrorBars.Format.Line.Weight = 2#
End With
End Sub
---------------------------------------------------
PLEASE NOTE that the target bar editing code DOES indeed have an effect - but not on the error bars in the X-direction! For example, if you change the "Amount:=0" setting above for the error bar Y-direction then you'll see that the Y-direction error bar turns red and has it's line weight set to 2. But that's not what I want; I want to modify the X-directional error bars.
If this is not clear, then please feel free to ask for more info.
Thanks!