Converting Column Object to Line Object Issue

**cocucraf** · 07-06-2009, 10:09 AM

Hi there,

I've created an Excel 2007 application that automatically creates a chart based on input by a user. A feature of this application is that the user can specify if a data series is to be represented as a line object or column object when creating a Line-Column chart. The feature is available when the user want to update an existing Line-Column chart.

The problem is that when the user wants to switch from a column object to a line object, the corresponding colour of the object is not maintained. It seems like during the process of altering the data series chart type from column to line, MS Excel applies a generic colour. However, when the data series goes from a line object to a column object, there is no issue.

Does anyone have any ideas as to why this is happening?

Thanks.

**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

**cocucraf** · 07-07-2009, 12:10 PM

That worked!! Yay!!!

So does that mean that MS Excel applies two levels of coloring to objects with one layer being more prominent than that other (hence setting transparency level to 0)?

**Andy Pope** · 07-08-2009, 04:15 AM

It's more to do with the new object model written for charts.

Unfortunately the order of setting properties and which ones need setting is not automatic or even logical.
Specifying a colour for the line should automatically make the line visible but it seems changing from automatic to custom colour leaves some properties unset or worst changed. So you have to apply them explicitly.

It all adds to the joy of chart programming.