Pie Charts

[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

[Jon Peltier]

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

[Dan]

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.

>.
>

[Andy Pope]

Hi Dan,

Has the workbook or any of the sheets been protected?
Jon's code worked for me until I protected the sheet.
(I had to unlocked the protection on one of the charts so I could select
a chart and run the macro.)

For completeness you might also want to add an Exit Sub to Jon's test of
whether a chart is active.

Dan wrote:

' Insert Here
Exit sub

--

Cheers
Andy

http://www.andypope.info