Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Data Series in a Pivot Chart
Hello,
I have 2 pivot charts, which both have the same data series, but depending on the selections made not all the data series will be used. I have recorded the code below from the macro recorderto format each series in a particular colour. But if say "Late" is not in the data selected, it gives me a run time error. How can I modify the code so that it will look for each data series and if not found go to the next data series. Sub FormatSeries () Application.ScreenUpdating = False ActiveChart.PlotArea.Select With Selection.Border .ColorIndex = 39 .Weight = xlThin .LineStyle = xlContinuous End With Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, _ Degree:=0.231372549019608 With Selection .Fill.Visible = True .Fill.ForeColor.SchemeColor = 15 End With ActiveChart.SeriesCollection("Early").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 27 .Pattern = xlSolid End With ActiveChart.SeriesCollection("Late").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveChart.SeriesCollection("OnTime").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With Application.ScreenUpdating = True End Sub Any help/assistance would be greatly appreciated. Winnie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Data Series in a Pivot Chart
I have realised this is more trouble than it is worth, so have decided to
create the graphs as non pivot.That way I will keep the formatting. Thanks "winnie123" wrote: Hello, I have 2 pivot charts, which both have the same data series, but depending on the selections made not all the data series will be used. I have recorded the code below from the macro recorderto format each series in a particular colour. But if say "Late" is not in the data selected, it gives me a run time error. How can I modify the code so that it will look for each data series and if not found go to the next data series. Sub FormatSeries () Application.ScreenUpdating = False ActiveChart.PlotArea.Select With Selection.Border .ColorIndex = 39 .Weight = xlThin .LineStyle = xlContinuous End With Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, _ Degree:=0.231372549019608 With Selection .Fill.Visible = True .Fill.ForeColor.SchemeColor = 15 End With ActiveChart.SeriesCollection("Early").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 27 .Pattern = xlSolid End With ActiveChart.SeriesCollection("Late").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveChart.SeriesCollection("OnTime").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With Application.ScreenUpdating = True End Sub Any help/assistance would be greatly appreciated. Winnie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Data Series in a Pivot Chart
You could use this:
Dim srs As Series For Each srs In ActiveChart.SeriesCollection Select Case srs.Name Case "Early" ' formatting for Early series Case "Late" ' formatting for Late series Case "On Time" ' formatting for On Time series Case Else ' formatting for any other series ' or just omit End Select You can also improve the efficiency of your code if you don't select objects. Change this: ActiveChart.SeriesCollection("Late").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With to this: With ActiveChart.SeriesCollection("Late") With .Border .Weight = xlThin .LineStyle = xlAutomatic End With .Shadow = False .InvertIfNegative = False With .Interior .ColorIndex = 3 .Pattern = xlSolid End With End With And since most of these settings are defaults recorded by the Macro Recorder, you can shorten this whole thing to one line: ActiveChart.SeriesCollection("Late").Interior.Colo rIndex = 3 - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ winnie123 wrote: Hello, I have 2 pivot charts, which both have the same data series, but depending on the selections made not all the data series will be used. I have recorded the code below from the macro recorderto format each series in a particular colour. But if say "Late" is not in the data selected, it gives me a run time error. How can I modify the code so that it will look for each data series and if not found go to the next data series. Sub FormatSeries () Application.ScreenUpdating = False ActiveChart.PlotArea.Select With Selection.Border .ColorIndex = 39 .Weight = xlThin .LineStyle = xlContinuous End With Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, _ Degree:=0.231372549019608 With Selection .Fill.Visible = True .Fill.ForeColor.SchemeColor = 15 End With ActiveChart.SeriesCollection("Early").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 27 .Pattern = xlSolid End With ActiveChart.SeriesCollection("Late").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveChart.SeriesCollection("OnTime").Select With Selection.Border .Weight = xlThin .LineStyle = xlAutomatic End With Selection.Shadow = False Selection.InvertIfNegative = False With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With Application.ScreenUpdating = True End Sub Any help/assistance would be greatly appreciated. Winnie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format data series (Chart) | New Users to Excel | |||
Format Data Series in a Chart | Excel Worksheet Functions | |||
Pivot Chart - X,Y & Data Series Options | Charts and Charting in Excel | |||
Format Series in Pivot Chart | Excel Programming | |||
Format Data Series in Pivot Chart | Excel Programming |