Macro to edit Error Bars

postal302

New Member
Joined
Oct 29, 2012
Messages
4
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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

New Member
Joined
Oct 29, 2012
Messages
4
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
This "turns off" the Y error bars.

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

postal302

New Member
Joined
Oct 29, 2012
Messages
4
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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.
 

Forum statistics

Threads
1,082,043
Messages
5,362,836
Members
400,694
Latest member
Sofie17

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top