Solved

VBA Creating Pie Chart

Posted on 2013-12-03
8
4,827 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now