Solved

VBA Creating Pie Chart

Posted on 2013-12-03
8
5,502 Views
Last Modified: 2013-12-04
Need a little assistance. Creating a pie chart and I have the following code

Sub CreatePie()

Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("RCSupport").Range("F1:H28"), PlotBy:= _
        xlColumns
    
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Scorecard (2)"
    ActiveChart.ChartTitle.Text = "Schedule Delay Averages # of Days/Store"
    ActiveChart.ShowAllFieldButtons = False
    ActiveChart.Legend.Delete
    ActiveChart.ApplyDataLabels xlDataLabelsShowValue, xlDataLabelsPercent
    ActiveChart.SeriesCollection(1).HasLeaderLines = False

End Sub

Open in new window


I can't figure out how to get the category name, value, and percent to all show on the pie chart and how to place the chart in a specific location would like it to be from a13:l42
0
Comment
Question by:jmac001
  • 5
  • 2
8 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39694530
Please attach your file here with additional ws for your wanted chart;

Thanks!
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 39694707
Hi,

pls try

Sub CreatePie()

Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("RCSupport").Range("F1:H28"), PlotBy:= _
        xlColumns
    
Set mychart = ActiveChart.Location(Where:=xlLocationAsObject, Name:="Scorecard (2)")
    mychart.ChartArea.Top = Range("A13").Top
    mychart.ChartArea.Left = Range("A13").Left
    mychart.ChartArea.Width = Range("A13:L42").Width
    mychart.ChartArea.Height = Range("A13:L42").Height

    mychart.ChartTitle.Text = "Schedule Delay Averages # of Days/Store"
    mychart.ShowAllFieldButtons = False
    mychart.Legend.Delete
    mychart.ApplyDataLabels xlDataLabelsShowValue, xlDataLabelsPercent
    mychart.SeriesCollection(1).HasLeaderLines = False
    With mychart.SeriesCollection(1).DataLabels
        .ShowCategoryName = True
        .ShowPercentage = True
        .Position = xlLabelPositionBestFit
        .Separator = ", "
    End With

End Sub

Open in new window

Regards
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39694781
This is my code:-

Sub CreatePie()

With Sheets("Scorecard (2)")
NumCharts = .ChartObjects.Count
If NumCharts > 0 Then
For I = NumCharts To 1 Step -1
.ChartObjects(I).Delete
Next I
End If
End With

Charts.Add
With ActiveChart
    .ChartType = xlPie
    .SetSourceData Source:=Sheets("RCSupport").Range("F1:H28"), PlotBy:=xlColumns
    .ChartTitle.Text = "Schedule Delay Averages # of Days/Store"
    .ShowAllFieldButtons = False
    .Legend.Delete
    .ApplyDataLabels xlDataLabelsShowValue, xlDataLabelsPercent
         With .SeriesCollection(1)
         .HasLeaderLines = False
         With .DataLabels
         .ShowCategoryName = True
         .ShowValue = True
         .ShowPercentage = True
         .Position = xlLabelPositionOutsideEnd
         End With
         End With
    .Location Where:=xlLocationAsObject, Name:="Scorecard (2)"
End With

With ActiveChart.Parent
         .Top = Range("A13").Top
         .Left = Range("A13").Left
         .Height = Range("13:42").Height
         .Width = Range("A:L").Width
End With

End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jmac001
ID: 39695846
Andrew_man and Rgonzo1971 both codes work but quick question is it possible to delete any of the data labels where the percent is 0?



Rgonzo1971 I  like the code because it would act as a refresh, you included:
 
.Position = xlLabelPositionOutsideEnd

Open in new window


How would I change this to have the data label appear on the the slice, I will be adding code to change the font to white?
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39695853
Okay, wait a minutes.
0
 
LVL 4

Assisted Solution

by:andrew_man
andrew_man earned 250 total points
ID: 39695909
Sub CreatePie()

With Sheets("Scorecard (2)")
NumCharts = .ChartObjects.Count
If NumCharts > 0 Then
For I = NumCharts To 1 Step -1
.ChartObjects(I).Delete
Next I
End If
End With

Charts.Add
With ActiveChart
    .ChartType = xlPie
    .SetSourceData Source:=Sheets("RCSupport").Range("F1:H28"), PlotBy:=xlColumns
    .ChartTitle.Text = "Schedule Delay Averages # of Days/Store"
    .ShowAllFieldButtons = False
    .Legend.Delete
    .ApplyDataLabels xlDataLabelsShowValue, xlDataLabelsPercent
         With .SeriesCollection(1)
         .HasLeaderLines = False
         With .DataLabels
         .ShowCategoryName = True
         .ShowValue = True
         .ShowPercentage = True
         .Position = xlLabelPositionOutsideEnd
         End With
         End With
    .Location Where:=xlLocationAsObject, Name:="Scorecard (2)"
End With
           
    For Each srs In ActiveChart.SeriesCollection
        With srs
               
            If .HasDataLabels Then
                
                nPts = .Points.Count
                avals = .Values
                For ipts = 1 To nPts
                     MsgBox avals(ipts)
                     
                    If avals(ipts) = 0 Then
                        .Points(ipts).HasDataLabel = False
                    End If
                Next
            End If
        End With
    Next
           
           
With ActiveChart.Parent
         .Top = Range("A13").Top
         .Left = Range("A13").Left
         .Height = Range("13:42").Height
         .Width = Range("A:L").Width
End With

End Sub

Open in new window

0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39695929
By the way, we should delete the old chart
0
 

Author Closing Comment

by:jmac001
ID: 39696070
Thank you both tweaked my code based on both of the codes provided and it is working as expected.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question