chart error bars

[Paula Galloway]

Has anyone had success with creating custom error bars in Excel 2007 from VBA?

In prior versions I was able to do so with

ActiveChart.SeriesCollection(4).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlCustom, Amount:="=MySheet!R2C17:R4C17"

When I try that code in 2007 I get error 1004, Application-defined or
object-defined error.

 

[Peter T]

How odd. It works fine but only if you do it like this

On Error Resume Next
' set the error bars
On Error Goto 0

Maybe one for Andy Pope !

Regards,
Peter T

 

[Peter T]

As I mentioned, the code worked for me under 'on error resume next'. However
to get it to work without forcing, it seems need to supply both error bars,
even if the minus are not required.

Set sr = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
sr.ErrorBar Direction:=xlY, _
Include:=xlBoth, _
Type:=xlCustom, _
Amount:="=Sheet1!R2C17:R4C17", _
MinusValues:="={0}"

Could also do
Amount:=Range("$Q$2:$Q$4")

Note, in the above, chart, series index and sheet name changed from your
original.

Regards,
Peter T

 

[Jon Peltier]

Peter -

You were right. Andy figured this one out for me some time ago, and it's now
part of my bag of tricks.

- Jon

 

[Peter T]

Andy figured this one out for me some time ago

I should have guessed

Regards,
Peter T

 

[Paula Galloway]

Thanks!

I should have guessed

Regards,
Peter T