?
Solved

VBA Creating Pie Chart

Posted on 2013-12-03
8
Medium Priority
?
7,028 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 54

Accepted Solution

by:
Rgonzo1971 earned 1000 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

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 1000 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

839 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