Sign in to follow this  
Followers 0
Zolteg

Manipulating objects on Excel Chart

1 post in this topic

G'day. I'm working with an Excel chart, and need to add two static lines on a chart at 80% and 90% value of a given figure. To do this, I'm using a method described at http://peltiertech.com/Excel/Charts/AddLineHorzErrBar.html

I'm running into a couple of problems with first pasting the data into the chart, turning the data point into an errorbar, and then colouring the errorbar.

I can work around the first two by kludging in Send commands, however for the life of me I cannot find how to change the colour of the error bar.

As background, the relevant excel macro would be:

Sub Macro1()
'
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/02/2011 
'

'
    Sheets("Sheet1").Select
    Range("D13:E13").Select
    Selection.Copy
    Sheets("Chart1").Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection.Paste Rowcol:=xlRows, SeriesLabels:=True, _
        CategoryLabels:=False, Replace:=False, NewSeries:=True
    ActiveChart.SeriesCollection(4).Select
    Application.CutCopyMode = False
    With Selection.Border
        .ColorIndex = 6
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlNone
        .MarkerForegroundColorIndex = xlNone
        .MarkerStyle = xlNone
        .Smooth = True
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.SeriesCollection(4).ErrorBar Direction:=xlX, Include:= _
        xlPlusValues, Type:=xlFixedValue, Amount:=31
    ActiveChart.SeriesCollection(4).ErrorBars.Select
    With Selection.Border
        .LineStyle = xlContinuous
        .ColorIndex = 6
        .Weight = xlMedium
    End With
    Selection.EndStyle = xlCap
End Sub

For the life of me I simply cannot get my script to select the errorbar created by my kludge, using the code below.

$oExcel.ActiveChart.SeriesCollection(3).ErrorBars.Select
    With $oexcel.Selection.Border
        .LineStyle = $xlContinuous
        .ColorIndex = 6
        .Weight = $xlMedium
    EndWith
    $oExcel.Selection.EndStyle = $xlCap
    $oExcel.ActiveChart.SeriesCollection(4).ErrorBars.Select
    With $oExcel.Selection.Border
        .LineStyle = $xlContinuous
        .ColorIndex = 46
        .Weight = $xlMedium
    EndWith
    $oExcel.Selection.EndStyle = $xlCap

Any pointers/tips/recodes welcome.

Share this post


Link to post
Share on other sites



Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0