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.

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

"Paula Galloway" <(E-Mail Removed)> wrote in message
news:83B0AC88-15AE-41CB-A385-(E-Mail Removed)...
> 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.

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


"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Paula Galloway" <(E-Mail Removed)> wrote in
> message news:83B0AC88-15AE-41CB-A385-(E-Mail Removed)...
>> 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 -

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>> "Paula Galloway" <(E-Mail Removed)> wrote in
>> message news:83B0AC88-15AE-41CB-A385-(E-Mail Removed)...
>>> 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.
>>
>>
>
>

> Andy figured this one out for me some time ago

I should have guessed :-)

Regards,
Peter T


"Jon Peltier" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services, Inc.
> http://PeltierTech.com/WordPress/
> _______
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>> "Paula Galloway" <(E-Mail Removed)> wrote in
>>> message news:83B0AC88-15AE-41CB-A385-(E-Mail Removed)...
>>>> 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.
>>>
>>>
>>
>>
>
>

Thanks!

"Peter T" wrote:

> > Andy figured this one out for me some time ago
>
> I should have guessed :-)
>
> Regards,
> Peter T
>
>
> "Jon Peltier" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services, Inc.
> > http://PeltierTech.com/WordPress/
> > _______
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:%(E-Mail Removed)...
> >> 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
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:(E-Mail Removed)...
> >>> 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
> >>>
> >>> "Paula Galloway" <(E-Mail Removed)> wrote in
> >>> message news:83B0AC88-15AE-41CB-A385-(E-Mail Removed)...
> >>>> 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.
> >>>
> >>>
> >>
> >>
> >
> >
>
>
>