Jump to content
Sign in to follow this  
Zolteg

Manipulating objects on Excel Chart

Recommended Posts

Zolteg

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.