Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart slice colours....almost works
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).Sele ct 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 -- _______________________ Naz, London |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart slice colours....almost works
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).Sele ct 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" wrote in message ... 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).Sele ct 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 -- _______________________ Naz, London |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart slice colours....almost works
Thanks for this...but i don't understand how to use this info to get the code
to work -- _______________________ Naz, London "Mike" wrote: 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).Sele ct 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" wrote in message ... 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).Sele ct 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 -- _______________________ Naz, London |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart slice colours....almost works
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. "Naz" wrote in message ... Thanks for this...but i don't understand how to use this info to get the code to work -- _______________________ Naz, London "Mike" wrote: 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).Sele ct 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" wrote in message ... 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).Sele ct 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 -- _______________________ Naz, London |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extra Slice in Pie in Pie chart | Charts and Charting in Excel | |||
Changing pie chart slice colors | Charts and Charting in Excel | |||
Pie Chart with other charts representing each slice | Charts and Charting in Excel | |||
Pie Chart - "Other" Slice | Excel Discussion (Misc queries) | |||
break a slice up in my pie chart into sub-catagories | Charts and Charting in Excel |