Excel Chart Challenge 1

[RobBroekhuis]

Hi all,
I'm trying to do the following:
I have an Excel x-y scatter chart, with time on the horizontal axis. The time spans several years, and I want to show the seasons on the chart as shaded areas, e.g. I'd like to put background shading in the area of the chart between 4/1/2001 and 6/30/2001. I have a fairly good idea what I could do in VBA to put rectangle objects onto the chart sheet, with absolute coordinates determined by a calculation using the chart size and x-axis spacing. That's not such a great way to go about it, though, because everytime the chart gets rescaled, the coordinates of the rectangle objects would have to be adjusted (and I don't think there are useful events to latch the code for doing that onto, so it would have to be triggered manually).
Is there some more elegant way of achieving the same result? I'm thinking perhaps a way of overlaying my chart onto a bar chart, or something else that automatically scales?
One other way, come to think of it, might be to "paint" the shaded area using an x-y line graph with a thick line weight. Very laborious, and probably slow, though.
Rob

 

[ide]

well,
- after your data range create a new range with your charts max y value.
- Copy the new data range and paste into the chart.
- Set the min and the max values of the scale (max above)

- type of series:
ActiveChart.SeriesCollection(...).ChartType = xlColumnClustered
- clear columns' border:
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With

- pattern and color of column:
Selection.Fill.Patterned Pattern:=msoPatternWave
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 18
.Fill.BackColor.SchemeColor = 2
End With

- set the column graph with to 0
With ActiveChart.ChartGroups(1)
.GapWidth = 0
End With

- set format axis/scale/Value (Y) axis crosses between categories to false
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.AxisBetweenCategories = False
End With

... But the last column will not end at the 6/30/2001 point, but between the 6/30/2001 and the 7/01/2001

i hope it wasn't too confusing
ide

 

[RobBroekhuis]

Ide,
Thanks for your suggestion. I tried this (pasted the series onto the existing graph, then selected this series, and from the immediate window in VBE typed

selection.charttype=xlcolumnclustered

Unfortunately, this generated a Visual Basic Run time error (80010108): "Automation error", something about a link to an object being lost. Upon clicking OK, I got a GPF that kicked me out of Excel. This behavior was reproducible. Seems like Excel didn't appreciate having the series charttype changed... What can I do to avoid this? Also: is there a way to achieve the same thing from the Excel user interface? I can't find how to change the charttype of a single series.
Note: My main graph is an x-y graph, which is fundamentally different in its treatment of the xvalues than a column graph - could this be the reason for the error?
Rob

 

[ide]

dear Rob,

sorry for my English
don't instead of type selection.cha.. set the type of the seriescollection
if you had only one series, after pasting the new the code has to be like
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
if your chart is currently selected

don't insert the code i pasted into your code, because the environment, ranges ... not equal at your project.

the code i wrote inserted just to give example values. just a root.

pls. try to record a new macro, and follow the steps above.

ide

 

[ide]

dear Rob,

sorry for my English
instead of type selection.cha.. set the type of the seriescollection
if you had only one series, after pasting the new the code has to be like
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
if your chart is currently selected

don't insert the code i pasted into your code, because the environment, ranges ... not equal at your project.

the code i wrote inserted just to give example values. just a root.

pls. try to record a new macro, and follow the steps above.

ide

 

[ide]

dear Rob,

sorry for my English
instead of typing selection.cha.. set the type of the seriescollection
if you had only one series, after pasting the new the code has to be like
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
if your chart is currently selected

don't insert the code i pasted into your code, because the environment, ranges ... not equal at your project.

the code i wrote inserted just to give example values. just a root.

pls. try to record a new macro, and follow the steps above.

ide

 

[ide]

ohh, it's horrid! i didn't want to send the first two reply. the system...

 

[RobBroekhuis]

Ide,
That's exactly what I did, although I did it through the immediate window. In other words, I selected the NEW SERIES on the chart, and checked in the immediate window to make sure that selection.name="Series2", then I used the selection.charttype assignment as you suggested. I don't think this would work any differently if I did it from a macro instead of from the immediate window.
Just to be sure, I tried it from a macro - I still get an error, and still get kicked out of Excel, but the error description is a little different (method charttype of object series failed), although the error number is still 80010108. In this case, I also get a "stack is full" error for some reason. I have no reason to believe my computer is limited in memory or resources, so I don't think the stack situation is the underlying problem.
Rob

 

[NigelHarper]

If you have a fixed period probably the simplest way to do this is to create a series of rectangles whose width is prorata'd according to the season against the total period and arrange them accordingly. Group them and then save as a export to a jpg format. Then just format the chart area patterns and from the fill effects choose picture and browse for your jpg file.
Obviously this only works for a fixed period but it is dirt simple and will simply scale up and down with the chart as it is resized.

 

[RobBroekhuis]

Nigel,
Agreed - that would be one solution. I'm not so much concerned about resizing the chart as reranging it, though - this is an ongoing time trend, so the time scale changes all the time (no pun intended). I'll play around a bit with this and see if I can find a reasonable way of automating it.
Thanks for your help
Rob