Finally managed to add a second Series to an Exploded Pie Chart.
But can't work out, how to have the whole procedure in one Macro.
How would I set 2 DataRanges in the first Macro?
Macro 1: CreateChart
Macro 2: AddSeries2
Macro 3: Format Series2 Labels+Explosion+AxisGroup, Series1 Explosion
Macro 4: Format Series1 Explosion
Also, I have to format the Series1 Explosion twice, Macros 3+4 (found out by clicking).
This can't be right, can it?
I've attached the xlsm.
Thanks again for any help!
Toni
Macro1: CreateChart
Option Explicit
Sub a1_CreateChart()
Dim objChart As ChartObject
Dim myChtRange As Range
Dim myDataRange As Range
With ActiveSheet
Set myChtRange = Range("C12:F28")
Set myDataRange = Range("C2:F3")
Set objChart = .ChartObjects.Add( _
Left:=myChtRange.Left, Top:=myChtRange.Top, _
Width:=myChtRange.Width, Height:=myChtRange.Height)
With objChart.Chart
.SetSourceData Source:=myDataRange
.SeriesCollection(1).Name = Range("C1")
.SeriesNameLevel = xlSeriesNameLevelCustom
.ChartType = xlPieExploded
.SeriesCollection(1).Explosion = 12
' .SeriesCollection(1).AxisGroup = 2
.HasLegend = False
.HasTitle = True
.ChartTitle.Left = 3
.ChartTitle.Top = 3
.ChartArea.AutoScaleFont = False
.SetElement msoElementDataLabelOutSideEnd
.SeriesCollection(1).DataLabels.ShowCategoryName = True
.SeriesCollection(1).DataLabels.ShowValue = True
.SeriesCollection(1).DataLabels.NumberFormat = "#.##0,00"
.SeriesCollection(1).DataLabels.Separator = vbNewLine
End With
End With
End Sub
Macro2: AddSeries2
Sub a2_AddSeries2()
ActiveSheet.ChartObjects("Chart 1").Activate 'SETs
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("C7")
.Values = ActiveSheet.Range("C3:F3")
.XValues = ActiveSheet.Range("C8:F8")
End With
End Sub
Macro 3: Format Series2 Labels+Explosion+AxisGroup, Series1 Explosion
Sub a3_Format_Series2_1()
ActiveSheet.ChartObjects("Chart 1").Activate 'SET
ActiveChart.SeriesCollection(1).Explosion = 12
ActiveChart.SeriesCollection(2).Explosion = 12
ActiveChart.SeriesCollection(1).AxisGroup = 2
Dim chrt As Chart, sr As Series, ds As DataLabels, ax As Axis
Set chrt = ActiveChart
Set sr = chrt.SeriesCollection(2)
sr.HasDataLabels = True
Set ds = sr.DataLabels
ds.ShowCategoryName = True
ds.ShowValue = False
ds.NumberFormat = "#.##0"
ds.Position = xlLabelPositionInsideEnd
ds.Font.color = RGB(255, 0, 0)
ds.Font.Bold = True
End Sub
Macro 4: Format Series1 Explosion
Sub a3_Format_Series2_2()
ActiveSheet.ChartObjects("Chart 1").Activate 'SET
ActiveChart.SeriesCollection(1).Explosion = 12
End Sub
Bookmarks