Pie Charts Vary In Size

  • Thread starter Thread starter DaveK
  • Start date Start date
D

DaveK

I am embedding multiple pie charts to illustrate the total
and free disk space for hard drives -- drive c:, drive d:,
etc. Each pie chart has only two values, total space and
remaining space. Each individual chart is OK, but each has
a different sized pie. I would like to force each chart
to be the same size, instead of having a dime sized, a
quarter sized and a penny sized pie. Is the size of the
pie somehow related to my total vs. free space or
something? I have tried to make the chart sizes the same
with the handles, but the charts seem to have a mind of
their own when it comes time to bake the pie.
 
Do you have data labels on the outside of the pie? Excel tries to make
room for these, so it shrinks the plot accordingly. You could have the
labels located in the center of each wedge, or you could write a macro
to stretch the plot area. Press Alt-F11 to open the VB Editor, then
press Alt-I then M to insert a module, and paste in this code. Back in
Excel, press Alt-F8 to run either FixActivePie to fix the active chart
or FixAllPies to fix all charts on the worksheet.

' -- Pie Chart Fixing Code -------------
Option Explicit

Const ChtAreaWidth As Integer = 300
Const ChtAreaHeight As Integer = 250

Sub FixActivePie()
If Not ActiveChart Is Nothing And ActiveChart.ChartType = xlPie Then
FixPies ActiveChart
Else
MsgBox "Select a pie chart and try again."
End If
End Sub

Sub FixAllPies()
Dim pie As ChartObject
For Each pie In ActiveSheet.ChartObjects
If pie.Chart.ChartType = xlPie Then
FixPies pie.Chart
End If
Next
End Sub

Sub FixPies(cht As Chart)
Dim i As Integer
With cht
.Parent.Width = ChtAreaWidth
.Parent.Height = ChtAreaHeight
For i = 1 To 4 ' Sometimes you have to repeat it??
.PlotArea.Top = 0
.PlotArea.Width = .ChartArea.Height
.PlotArea.Left = (.ChartArea.Width - .PlotArea.Width) / 2
.Legend.Position = xlLegendPositionRight
Next
End With
End Sub
' -- End of Code --------------------

- Jon
 
Back
Top