Solved

VBA Creating Pie Chart

Posted on 2013-12-03
8
5,063 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 49

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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

809 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