Pie Chart

[Francis Ang]

I've managed to represent the following information in a pie chart using VBA
but is unable to display the information in Column A as Category Name in the
pie chart-

Column A Column B
Apples 100
Oranges 350
Pears 200
Plums 400
Mangoes 700

Can anyone please, show how to include the information in Column A as
Category Name using VBA. Thank you.

 

[ryguy7272]

I just recorded a macro; results below:
Sub Macro1()

Range("A1:B6").Select
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B6"),
PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Fruits"
End With
End Sub


Regards,
Ryan---

 

[Francis Ang]

Thank you for the help. I had the same result when I recorded the macro.

However, the macro does not show how to label each of the pie slices with
the fruit names in Column A.

 

[Peter T]

Where do you expect the Category Labels to be displayed, in the Legend or as
Data Labels (wouldn't normally want both).

Regards,
Peter T

 

[Francis Ang]

Hi Peter,

I need to display the category label as Data Label.

Thanks.

 

[Peter T]

I recorded a macro, cleaned it up a bit to remove select & activate stuff
(which you don't need when programatically creating or changing charts),
included a few fully declared object references to get the intellisense, and
ended up with something like this -

Sub test()
Dim cht As Chart
Dim sr As Series
Dim dls As DataLabels

' you've probably already got a ref to your chart so you won't need
' this first line

Set cht = ActiveSheet.ChartObjects(1).Chart ' 1st chartobject.chart on
sheet
' or if a chart sheet
' Set cht = ActiveWorkbook.Charts("Chart1")

Set sr = cht.SeriesCollection(1)

' see recorded macro for other label arg's you might want
sr.ApplyDataLabels AutoText:=True, _
ShowCategoryName:=True

Set dls = sr.DataLabels
With dls
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Position = xlLabelPositionBestFit
.Orientation = xlHorizontal
End With

End Sub

Regards,
Peter T

 

[Francis Ang]

Hi Peter,

Thank you very much for all your help. The code that you gave was what I
wanted. Thank you.