macro to create pie chart

hi - need some help with my existing pie chart creation macro in vba. everything works just fine just having an issue where sometimes the data labels on the pie chart show up on top of each other if items are too close. anyway to separate these?

also, would like to include the sum on the pie chart itself, is this possible?

lastly, id like to rotate the axis on the pie a bit more, any direction on the best way to do this?

Thanks!


Sub CHART()
''Build Chart
'Dim rg As Range, rgCopy As Range
'Dim ShapeCnt As Integer, ChtNum As Integer
'
'With ws
'    'ChartTitle = ws.Name
'    Worksheets("test chart page").Select
'    Set rng = Range("A1", Range("B" & Rows.Count).End(xlUp))
'    'Range("A2:B6").Select
'    rng.Select
'    ActiveSheet.Shapes.AddChart.Select
'    'ActiveChart.SetSourceData Source:=Range("'test chart page'!$A$2:$B$6")
'    ActiveChart.SetSourceData Source:=rng
'    ActiveChart.ChartType = xl3DPieExploded
'    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
'    ActiveChart.ChartTitle.Text = "test"
'    Range("D5").Select
'End With
Dim ShapeCnt As Integer, ChtNum As Integer
 'Worksheets("test chart page").Select
    Set rng = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Set Rng2 = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Rng2.NumberFormat = "#,##0"
    rng.Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xl3DPieExploded
    ShapeCnt = ActiveSheet.Shapes.Count
    For ChtNum = 1 To ShapeCnt
    With ActiveSheet.Shapes(ChtNum).Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(153, 204, 255)
        .Solid
    End With
        With ActiveSheet.Shapes(ChtNum).Line
        .Visible = msoTrue
        .Weight = 2
    End With

'Add Data Label Series Value by default
    ActiveChart.ApplyDataLabels
''Add Percentage to Data Label
'    ActiveChart.SeriesCollection(1).DataLabels.Select
'    Selection.ShowPercentage = True
    
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
'You can either use manually entered text for the Chart Title or use object name like below
'    ActiveChart.ChartTitle.Text = "test"
    ActiveChart.ChartTitle.Text = ActiveSheet.Name
    
    Next
End Sub

Open in new window

eastsidemarketAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

    ActiveChart.SeriesCollection(1).DataLabels.Position = xlLabelPositionOutsideEnd
    ActiveChart.ChartGroups(1).FirstSliceAngle = 10 ' 0 and 360 degrees
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, _
        166.5, 170.25, 90, 14.25).TextFrame.Characters.Text = "Total: " & WorksheetFunction.Sum(Rng2)

Open in new window

Regards
0
eastsidemarketAuthor Commented:
thanks. the sum is now where, but not formatted like how rng2 is. i want no decimals with commas.

also the data labels are still on top of each other . anyway to get them separated?
0
Rgonzo1971Commented:
Hi,

Could you send a dummy image of your pie chart?

for the decimals, pls use  
  ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, _
        166.5, 170.25, 90, 14.25).TextFrame.Characters.Text = _
        "Total: " & Format(WorksheetFunction.Sum(Rng2), "#,##0")

Open in new window

Regards
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rgonzo1971Commented:
Hi,

One way to solve this problem is to change the legends to add the labels

    For Each c In Rng2
        strLgnds = strLgnds & c.Offset(0, -1).Text & ": " & Format(c.Value, "#,##0") & ","
    Next
    strLgnds = Left(strLgnds, Len(strLgnds) - 1)
    aLgnds = Split(strLgnds, ",")
    ActiveChart.SeriesCollection(1).XValues = aLgnds

Open in new window

Regards
0
eastsidemarketAuthor Commented:
that didn't work too good rgonzo. the decimal piece works great.

It still shows labels on top of each other if they are too close.

See sample pie chart:

Thanks!

Current Code:
Sub CHART()
''Build Chart
'Dim rg As Range, rgCopy As Range
'Dim ShapeCnt As Integer, ChtNum As Integer
'
'With ws
'    'ChartTitle = ws.Name
'    Worksheets("test chart page").Select
'    Set rng = Range("A1", Range("B" & Rows.Count).End(xlUp))
'    'Range("A2:B6").Select
'    rng.Select
'    ActiveSheet.Shapes.AddChart.Select
'    'ActiveChart.SetSourceData Source:=Range("'test chart page'!$A$2:$B$6")
'    ActiveChart.SetSourceData Source:=rng
'    ActiveChart.ChartType = xl3DPieExploded
'    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
'    ActiveChart.ChartTitle.Text = "test"
'    Range("D5").Select
'End With
Dim ShapeCnt As Integer, ChtNum As Integer
 'Worksheets("test chart page").Select
    Set Rng = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Set Rng2 = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Rng2.NumberFormat = "#,##0"
    Rng.Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xl3DPieExploded
    ShapeCnt = ActiveSheet.Shapes.Count
    For ChtNum = 1 To ShapeCnt
    With ActiveSheet.Shapes(ChtNum).Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(153, 204, 255)
        .Solid
    End With
        With ActiveSheet.Shapes(ChtNum).Line
        .Visible = msoTrue
        .Weight = 2
    End With

'Add Data Label Series Value by default
    ActiveChart.ApplyDataLabels
''Add Percentage to Data Label
'    ActiveChart.SeriesCollection(1).DataLabels.Select
'    Selection.ShowPercentage = True
    
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
'You can either use manually entered text for the Chart Title or use object name like below
'    ActiveChart.ChartTitle.Text = "test"
    ActiveChart.ChartTitle.Text = ActiveSheet.Name
         'test code
     ActiveChart.SeriesCollection(1).DataLabels.Position = xlLabelPositionOutsideEnd
    ActiveChart.ChartGroups(1).FirstSliceAngle = 10 ' 0 and 360 degrees
     ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, _
        166.5, 170.25, 90, 14.25).TextFrame.Characters.Text = _
        "Total: " & Format(WorksheetFunction.Sum(Rng2), "#,##0")
    Next
'    'TEST LABELS
'    Dim strLgnds As String
'     For Each c In Rng2
'        strLgnds = strLgnds & c.Offset(0, -1).Text & ": " & Format(c.Value, "#,##0") & ","
'    Next
'    strLgnds = Left(strLgnds, Len(strLgnds) - 1)
'    aLgnds = Split(strLgnds, ",")
'    ActiveChart.SeriesCollection(1).XValues = aLgnds
    
End Sub

Open in new window

Book4.xlsx
0
Rgonzo1971Commented:
Hi,,

finding an algorithm to stop labels overlapping is almost impossible, maybe you could change your chart type to horizontal bars

Regards
0
eastsidemarketAuthor Commented:
that's not a bad idea.

would all I need to change is?
 ActiveChart.ChartType = xl3DPieExploded

if so, what would i replace xl3DPieExploded with?
0
Rgonzo1971Commented:
Hi

'    ActiveChart.ChartType = xl3DBarClustered ' or xlCylinderBarClustered

and delete 'ActiveChart.SeriesCollection(1).DataLabels.Position = xlLabelPositionOutsideEnd

Regards
0
Rgonzo1971Commented:
Hi

you can add a datatable for more visibility not possible with pie chart
    ActiveChart.SetElement (msoElementDataTableShow) or
    ActiveChart.SetElement (msoElementDataTableWithLegendKeys)

Regards
0
eastsidemarketAuthor Commented:
the datatable doesnt look good b/c theres too much data!! but this definately looks better, thanks.

few questions on this.

#1, how do I remove the legend?
#2, how can i auto size the chart so that it fits all the data?

thanks!
0
Rgonzo1971Commented:
Hi,

to remove legends
ActiveChart.Legend.Delete

Open in new window

To resize and displace your graph
     With ActiveChart.Parent
         .Height = 400 ' resize
         .Width = 600  ' resize
         .Top = 150    ' displace
         .Left = 150   ' displace
     End With

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eastsidemarketAuthor Commented:
looks great, thanks for all the help!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.