Editing a chart's error bars...

[DashBoarder]

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!

 

[DashBoarder]

Excel 2010, 64-bit (14.0.7015.1000)
Win8 Pro

(NOTE that I've re-posted my code below so that it's (hopefully) easier to read. My apologies for not first reading the forum faq.)

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!

Dashboarder