Converting Column Object to Line Object Issue

**Andy Pope** · 07-06-2009, 10:14 AM

How are you changing chart type?

The line should retain the column series Border colour as it's line colour.

Do you have Office SP2 installed?

**cocucraf** · 07-06-2009, 01:56 PM

Originally Posted by Andy Pope

How are you changing chart type?

The line should retain the column series Border colour as it's line colour.

Do you have Office SP2 installed?

I'm simply setting the series collection's chart type property from xlColumnClustered to xlLine. My tool's requirements specify that column objects should have no border, but I am reapplying the appropriate colour afterwards in a separate subroutine. I am running Excel 2007 SP2.

**Andy Pope** · 07-07-2009, 04:12 AM

But if your column has no border what colour should it use when switching to line. I assume excel just takes a stab at a colour perhaps using the automatic default colours.

**cocucraf** · 07-07-2009, 08:59 AM

I have a subroutine called "SeriesColorAllocation" which assigns a client-specified list of colors to my data series objects (i.e., line, column, area) based on their position on the worksheet. The subroutine is called after the data series' chart object has been changed. The main color assignment logic is as follows:

chtActiveChart.SeriesCollection(i).Select
With SelectionIf .ChartType <> xlLine Then.Format.Fill.ForeColor.RGB = garrColorPalette(i)
.Border.ColorIndex = xlColorIndexNone
Else.Format.Line.ForeColor.RGB = garrColorPalette(i)
End If
End With

The "garrColorPalette" array object stores the RGB values specified by the client.

**Andy Pope** · 07-07-2009, 09:03 AM

Sorry I'm confused now.

If you are coding all this can you not add code to set the colors you need?

Or is the problem that the setting of colours is not working?

**cocucraf** · 07-07-2009, 10:49 AM

My apologies for the confusion. Yes, the setting of the colors does not work for the specific instance whereby the user wants to switch from the current column object to the desired line object.

**Andy Pope** · 07-07-2009, 11:28 AM

Try this

chtActiveChart.SeriesCollection(i).Select
With Selection

    If .ChartType <> xlLine Then

        .Format.Fill.ForeColor.RGB = garrColorPalette(i) .Border.ColorIndex = xlColorIndexNone

    Else

        .Format.Line.transparency = 0
        .Format.Line.ForeColor.RGB = garrColorPalette(i)

    End If

End With