Adding data labels to charts

[rulerz]

Hi,
I have used the following code to generate a chart in my sheet:
--Code
Sub MakeAPie()
Dim title As String
Dim getstr1 As String
Dim getstr2 As String
title = "A1"
getstr1 = "A3:A9"
getstr2 = "E3:E9"
Dim chtOb As ChartObject
Set chtOb = ActiveSheet.ChartObjects.Add(100, 100, 250, 175)
With chtOb.Chart
.ChartType = xlPie
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = ActiveSheet.Range(getstr1)
.SeriesCollection(1).Values = ActiveSheet.Range(getstr2)
.SeriesCollection(1).Name = ActiveSheet.Range(title)
End With
End Sub
--Code
But I am not able to figure out how to add data labels (category &
percentage) to the chart generated above. And also I get the chart as
an object in my active sheet. How do I write the code to get it to
display in a new sheet?
Would appreciate any help on this one.
Thanks,

 

[Bernie Deitrick]

rulerz,

Try the version below.

HTH,
Bernie
MS Excel MVP


Sub MakeAPie2()
Dim title As String
Dim getstr1 As String
Dim getstr2 As String
title = "A1"
getstr1 = "A3:A9"
getstr2 = "E3:E9"
Dim chtOb As ChartObject
Set chtOb = ActiveSheet.ChartObjects.Add(100, 100, 250, 175)
With chtOb.Chart
.ChartType = xlPie
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = ActiveSheet.Range(getstr1)
.SeriesCollection(1).Values = ActiveSheet.Range(getstr2)
.SeriesCollection(1).Name = ActiveSheet.Range(title)
.SeriesCollection(1).ApplyDataLabels ShowCategoryName:=True, _
ShowPercentage:=True
.Location Where:=xlLocationAsNewSheet
End With
End Sub

 

[rulerz]

Thanks a loooooot Bernie...
That was 'exactly' what I wanted.
It executed perfectly