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.