Pie Charts

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi. I have about 70 Pie Charts (with Titles) spread over
7 worksheets in a file. I would like them all to be the
same size, however, whatever I do them they just resize
and reshape (they get smaller, become oval etc.). This
happens spontaneously and even after I've locked the
worksheet to protect it. It's driving me nuts. Any
ideas? Someone I know has suggested using a macro - but
I'm not good enough to write one capable of fixing this.

Dan
 
Dan -

Press Alt-F11 to open the VB Editor, press Alt-I then M to insert a code
module, and paste the code below into the code window.

Select a pie chart that looks right (adjust it first if necessary), then
run the macro.

Sub FixPies()
Dim WkSht As Worksheet
Dim ChtOb As ChartObject
Dim dChtObHt As Double
Dim dChtObWd As Double
Dim dPltArHt As Double
Dim dPltArWd As Double
Dim dPltArTp As Double
Dim dPltArLf As Double
'' Is a chart selected?
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again", vbExclamation, _
"No Chart Selected"
End If
'' Get "Good" Parameters
With ActiveChart
If .ChartType <> xlPie Then
MsgBox "Select a pie chart and try again", _
vbExclamation, "Wrong Chart Type"
Exit Sub
End If
dChtObHt = .Parent.Height
dChtObWd = .Parent.Width
dPltArHt = .PlotArea.Height
dPltArWd = .PlotArea.Width
dPltArTp = .PlotArea.Top
dPltArLf = .PlotArea.Left
End With
'' Apply "Good" Parameters to Pie Charts
For Each WkSht In ActiveWorkbook.Worksheets
For Each ChtOb In WkSht.ChartObjects
With ChtOb
If .Chart.ChartType = xlPie Then
.Height = dChtObHt
.Width = dChtObWd
With .Chart
.PlotArea.Height = .ChartArea.Height / 3
.PlotArea.Width = .ChartArea.Width / 3
.PlotArea.Top = dPltArTp
.PlotArea.Left = dPltArLf
.PlotArea.Height = dPltArHt
.PlotArea.Width = dPltArWd
End With
End If
End With
Next
Next
End Sub


- Jon
 
Hi Jon. Thanks for the macro. Unfortunately when I run
it it gives me a 'Run-time error 1004, unable to set the
height property of the Plotarea class' - When I got to
debug it highlights the folloiwing line:
.PlotArea.Height = .ChartArea.Height / 3
Any ideas why?

Thanks. Dan.
 
Back
Top