Macro to edit Error Bars

[postal302]

Hello all!

I need to figure how to control either the X or Y axis error bars for a scatter chart.

I'm building something like a gantt chart and I only want to use the X axis error bars and delete the Y axis bars. I've been able to add error bars by using "ActiveChart.SeriesCollection(1).HasErrorBars = True" but when i select the error bars using "ActiveChart.SeriesCollection(1).ErrorBars.Select" the Y axis (Vertical Error Bars) are automatically selected and any formatting I apply is added to the Y axis and left off of the X axis.

Does anyone know how to delete the Y axis error bars and/or choose only the X axis error bars?

Thanks in advance.

 

[AlphaFrog]

Hi and welcome to the forum.

Edit the error bars while recording a macro. The recorded macro often will show you the VBA commands and syntax you're looking for.

 

[postal302]

That is a great suggestion, and for most cases has worked for me, but in this case it has fallen short. When adding error bars and formatting them (including changing the line color to gradient, setting the colors and stops and setting the line thickness) this is what the macro recorder fed back:

ActiveChart.SeriesCollection(1).HasErrorBars = True
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).ErrorBars.Select
Selection.DELETE
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).ErrorBars.Select
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _
xlPlusValues, Type:=xlFixedValue, Amount:=1
ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:= _
xlPlusValues, Type:=xlCustom, Amount:=0
ActiveSheet.ChartObjects("Chart 1").Activate

The problem comes in with the "Selection.DELETE" command. I specifically chose 'Series "1" Y Error Bars' in the "current Selection" drop-down menu of the Excel Ribbon, then pushed the delete key, and that was the command recorded. when attempting to re-run the code, it errors on the next line after "Selection.DELETE." because all error bars (X and Y) have been deleted.

I'm wondering if there's a way to write: "ActiveChart.SeriesCollection(1).ErrorBar(Direction:=xlY).DELETE".

Again, thanks for the help!

 

[AlphaFrog]

This "turns off" the Y error bars.

    ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, [COLOR=#ff0000]Include:=xlNone[/COLOR], _
        Type:=xlPercent, Amount:=5

 

[postal302]

Excellent!! That worked perfect! Sorry for the long delay in replying about the success, I have been redirected to "put out" many "fires" since I was last working on this project. Your help on this has been HUGE!!!

Now that I can edit the x-axis lines, I want to apply gradient colors to them. I can do this manually, but it's taking way too long for the scope of the project I'm working on. I've been able to change the color by:

ActiveChart.SeriesCollection(A).ErrorBars.BORDER.ColorIndex = 5

But I haven't been able to change to gradient color. With all the searching I've done I'm confident I can set the number of stops, location for the stops and color of stops, but I just cant "turn on" gradient color.

I know this chart is getting way complicated, but I'm a guy about presentation and want it to look right.

Thanks again in advance!

 

[AlphaFrog]

I don't think you will be able to set the errorbars to show gradient color.

Have you looked at Jon Peltier's Advanced Gantt Charts. You could define gradient colors using that Ganttt chart method.