Chart slice colours....almost works

[Naz]

Hi,

I want to create a little macro that automatically changes the colours of
each slice in a chart to a pre-defined colour. I can't create a pre-defined
user chart for various network reasons. So far I have the following long
winded, but easy for me to understand code, but the case select module where
the colours are selected doesn't work, i've only had a rough guess as to how
it should work, and if you can do a case select for chart slices, so i may be
well out.

Sub ChartColours()

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
Slice = 1

On Error Resume Next

For x = 1 To 20 ' Im going to assume no more than 20 slices

ActiveChart.SeriesCollection(1).Points(Slice).Select
Module1.SelectionFill
Module1.SliceColour

Slice = Slice + 1

Next x

End Sub

Private Sub SelectionFill()

Selection.Fill.OneColorGradient Style:=msoGradientDiagonalUp,
Variant:=2, _
Degree:=0.231372549019608

End Sub

Private Sub SliceColour()

SelSlice = Chart.SeriesCollection(1).Points <<< I'm stuck at
this point

Select Case SelSlice

Case 1
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 1
End With

Case 2
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 2
End With

Case 3
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 3
End With

Case 4
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 4
End With

Case 5
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 5
End With

End Select


End Sub


Any help to get it working would be greatly appreciated, help to make it
work and more efficient would be even more appreciated !


regards

 

[Mike]

Hi Naz,

the first thing that pops into my head is that on a pie chart, isn't each
slice a separate series, not point of series?

If so, then you would want to work with the series, not the point
in your main sub, instead of
ActiveChart.SeriesCollection(1).Points(Slice).Select
use
ActiveChart.SeriesCollection(Slice).Select

And in your sub SliceColour,

SelSlice = Chart.SeriesCollection(1).Points
is going to select the points of the first series every time you call the
sub.


More ideally, you should try defining your sub SelectionFill and SliceColour
with a variable, like (not tested, and again assuming that a pie chart slice
is a series, not a point)

Sub SelectionFill(TargetSeries as Series)
TargetSeries.Fill.OneColorGradient _
Style:=msoGradientDiagonalUp, _
Variant:=2, _
Degree:=0.231372549019608
End sub

and call it with selectionFill
targetseries:=activechart.seriescollection(slice)

By not using select, you'll help your code run faster (and I always get into
trouble when I use .select)

Do the same with your SliceColour.

HTH
Mike.

 

[Naz]

Thanks for this...but i don't understand how to use this info to get the code
to work

 

[Mike]

Hi Naz,

I took a better look at your problem just now.

For starters, the first line in your code module should be option explicit,
that will save you a lot of problems - it will make sure that you're
defining and assigning to variables correctly and when using "On Error
Resume Next", you should cancel that with a "On error goto 0", and try and
minimize the range of code that you apply that to. Also, ignore my
(incorrect) guess about each slice being a separate series, now that I'm in
front of excel I see I'm wrong.

As for the point where you identify that you're stuck, when you're trying to
assign an object to a variable, you need to use set, as in "Set selslice =
ActiveChart.SeriesCollection(1).Points", but because of the "On Error Resume
Next" that you'd called earlier, the error message doesn't pop up warning
you about this.

Assuming that you don't have some plan behind the schemecolor other than
slice number 1 is colour # 1, slice 2 = colour 2, etc, I think what you're
looking for is done in one sub like this:

Sub ChartColours()
Dim x As Long
With ActiveSheet.ChartObjects("Chart 1").SeriesCollection(1)
For x = 1 To .Points.Count 'doesn't matter how many points
With .Points(x).Fill
.OneColorGradient _
Style:=msoGradientDiagonalUp, _
Variant:=2, _
Degree:=0.231372549019608
.Visible = True
.ForeColor.SchemeColor = x
End With
Next x
End With
End Sub

HTH and sorry I didn't look this closely the first time,

Mike.