Re: xy scatter series formatting

I've never seen shading to the left, but Jon Peltier has instructions
for shading below a series:

http://www.geocities.com/jonpeltier...uff.html#XYArea

He also has an add-in that will switch the x and y series:
http://www.geocities.com/jonpeltier...s.html#SwitchXY

ian Mangelsdorf wrote:
> Is it possible to shade the area to the left of a xy series back to the Y
> axis. ie much the same as a area chart.
>
> I need xy scatter to be able to scale my charts.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks for the plug, Deb. But my XY Area chart trick will only fill
beneath an XY chart, and the SwitchXY routine only works on XY charts,
not line or area charts.

However, you can draw shapes on a chart using VBA. The following
routine will draw a polygon, starting with a horizontal line to the
first point of the first series on the active chart, then to each
subsequent point in the series, then from the last point horizontally
back to the axis. Then it fills in the polygon with a color. The
polygon covers the series, so the markers and the line itself are
partially hidden by the shape.

Nothing's perfect. It doesn't account for axes that are plotted in
reverse order. It doesn't warn you if there is no chart selected. Etc.

Anyway, here's the code:

''' START THE CODE ---------------------------------------
Sub ShadeLeft()
Dim myCht As Chart
Dim mySrs As Series
Dim Npts As Integer, Ipts As Integer
Dim myBuilder As FreeformBuilder
Dim myShape As Shape
Dim Xnode As Double, Ynode As Double
Dim Xmin As Double, Xmax As Double
Dim Ymin As Double, Ymax As Double
Dim Xleft As Double, Ytop As Double
Dim Xwidth As Double, Yheight As Double

Set myCht = ActiveChart
Xleft = myCht.PlotArea.InsideLeft
Xwidth = myCht.PlotArea.InsideWidth
Ytop = myCht.PlotArea.InsideTop
Yheight = myCht.PlotArea.InsideHeight
Xmin = myCht.Axes(1).MinimumScale
Xmax = myCht.Axes(1).MaximumScale
Ymin = myCht.Axes(2).MinimumScale
Ymax = myCht.Axes(2).MaximumScale

Set mySrs = myCht.SeriesCollection(1)
Npts = mySrs.Points.Count

Xnode = Xleft
Ynode = Ytop + (Ymax - mySrs.Values(1)) * Yheight / (Ymax - Ymin)

Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
For Ipts = 1 To Npts
Xnode = Xleft + mySrs.XValues(Ipts) * Xwidth / (Xmax - Xmin)
Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin)
myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
Next

Xnode = Xleft
Ynode = Ytop + (Ymax - mySrs.Values(Npts)) * Yheight / (Ymax - Ymin)
myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode

Xnode = Xleft
Ynode = Ytop + (Ymax - mySrs.Values(1)) * Yheight / (Ymax - Ymin)
myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode

Set myShape = myBuilder.ConvertToShape

With myShape
' USE YOUR FAVORITE COLORS HERE
.Fill.ForeColor.SchemeColor = 13 ' YELLOW
.Line.ForeColor.SchemeColor = 12 ' BLUE
End With

End Sub
''' END THE CODE -----------------------------------------

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Debra Dalgleish wrote:
> I've never seen shading to the left, but Jon Peltier has instructions
> for shading below a series:
>
> http://www.geocities.com/jonpeltier...uff.html#XYArea
>
> He also has an add-in that will switch the x and y series:
> http://www.geocities.com/jonpeltier...s.html#SwitchXY
>
> ian Mangelsdorf wrote:
>
>> Is it possible to shade the area to the left of a xy series back to the Y
>> axis. ie much the same as a area chart.
>>
>> I need xy scatter to be able to scale my charts.
>
>

Thanks Deb and Jon

ill give it a go

Cheers

Ian
"Debra Dalgleish" <dsd@contextures.com> wrote in message
news:3F09ECA1.8090509@contextures.com...
> I've never seen shading to the left, but Jon Peltier has instructions
> for shading below a series:
>
> http://www.geocities.com/jonpeltier...uff.html#XYArea
>
> He also has an add-in that will switch the x and y series:
> http://www.geocities.com/jonpeltier...s.html#SwitchXY
>
> ian Mangelsdorf wrote:
> > Is it possible to shade the area to the left of a xy series back to the
Y
> > axis. ie much the same as a area chart.
> >
> > I need xy scatter to be able to scale my charts.
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>