Excel vba, change series type from line to area and back cannot modify line properties

Question

Dynamically Change Chart Configurations

Combo Chart with a mix of Line and Area series Type. VBA code changes Type of each series based on a calculated Table in the worksheet Class Module to access series properties and methods to manipulate them. Currently, just select chart type from drop-down list and use the change event on the worksheet to parse each chart and change atributes on each chart in turn.

Atributes changed:

series Type (xlArea,xlLine)
series Format.Line.Forecolor.RGB 
       Format.Fill.Forecolor.RGB 
       (Long: colours based cell.Interior.Color)

series Format.Line.Width (Single)

series Format.Fill.Transparency
       Format.Line.Transparency (Double: 0 to 1)
series Pattern for area Type
       seriesname.Format.Fill.Patterned if required  (MsoPatternType)
       seriesname.Format.Fill.Solid if not required

Problem Statement

For my Chart type 1, series 2 is a XLline, for chart type 2 its is an XLarea

I have been running with chart type 1 for many weeks and dynamically changing the colours and transparancy and chart visibility The series Values and XValues are named ranges which are dynamicaly pointed to the apropriate vectors (range object)

I now introduce chart type 2

When I switch to chart type 2 and back to chart type 1 I can no longer set the line colours, transparency, or width settings.

I have some combo charts in an excel application and I want to dynamically change the charts in response to user input. I have succeeded in doing this and all was working fine, until I tried to dynamically change the series Type from Line to Area for one mode and then back again to the original configuration as required.

Behaviour

If I manually select the offending series and open the format dialog and slightly change the transparency setting (just a nudge off zero is all it takes), the weight and colour settings jump back to the original settings and is once more able to be changed by VBA.

I can also achieve the same thing by selecting "no Line" in the line options, again manually. When I click on No Line it just blinks for a second but keeps the Solid Line setting. If I click No Line as second time, it disappears as you would expect.

Its as if something is released by manually nudging the format settings...

I have a feeling that its got something to do with borders and also some legacy problem. Its like the line has borders after its life as an area and its never the same again. I think the line object you get after you transform from an area is maybe a couple of sandwiches short of a picnic... I think it must be defaulting to a 2003 type format object, but I cant figure out how to manage it.

I am using excel 2013 on windows 7.

I checked that the the line was set to visible and I also noticed that the Format.Fill.Visible was msoFalse on the offending line compared to true on another line on the chart (which had not been transformed out of its original type).

Ive been managing all of the charts using class modules including the following:

Property Let sType(SeriesType As Long)
    With CurrChart.SeriesCollection(Indx)
        .Type = SeriesType
    End With
End Property

Public Sub noMarkers()
    With CurrChart.SeriesCollection(Indx)
        .MarkerStyle = xlMarkerStyleNone
    End With
End Sub


Property Let LineWeight(w)

With CurrChart.SeriesCollection(Indx)
    .Format.Line.Weight = w
End With
End Property

Property Let LineVisible(v)
With CurrChart.SeriesCollection(Indx)
    .Format.Line.Visible = v
End With
End Property

As mentioned above, all have been going quite well until I struck this. I'm sure I'm doing something stupid so I hope someone can suggest a way forward.

If anyone has code for enumerating series object that would help so I could tabulate and compare the differences in the series' that work and the ones that don't.

Any advice would be greatly appreciated.

Cool Blue Cool Blue · Accepted Answer · 2013-10-28T08:33:28

Its amazing what 5 hours sleep can do for you...

The problem was caused by the series.Border and the series.Format.Line.Visible properties not being set correctly when reverting to XLline type.

I was switching line visibility off for the area mode and not switching it back on when reverting to a line. I also had to set series.Border.LineStyle = xlNone when reverting to a line type (XLHairline also works). Borders were switched on, as a side effect of changing to XLArea type.

The line I was seeing was the series border sitting on top of the actual line and obscuring it. So when I modified the attributes of the underlying line, there was no visible change.

The border properties, which were causing the problem, are not documented in the on-line documentation in MSDN and are not visible in the drop-down list of available objects in VBE. I guess this is a legacy conundrum that needs to be worked around.

Excel vba, change series type from line to area and back cannot modify line properties

1 Answers