how do i enlarge a pie chart by a fixed percentage?

[ellen s.]

created a pie chart, and figured out how to drag the corner of plot area to
enlarge the chart size free-hand. how do i increase the chart size by
exactly 20% to show an increase of 20% in total business size over last
year's pie chart?

 

[Andy Pope]

Hi,

I think you have to resort to code to do this.

Sub x()
'
' enlarge pie plot area by % of current size
'
Dim sngLeft As Single
Dim sngTop As Single
Dim sngWidth As Single

With ActiveChart.PlotArea
sngLeft = .Left
sngTop = .Top
' move to top corner to allow for growth
.Left = 1
.Top = 1
sngWidth = .Width
.Width = sngWidth * 1.2 ' 20% enlargement
.Left = sngLeft + ((sngWidth - .Width) / 2)
.Top = sngTop + ((sngWidth - .Width) / 2)
End With

End Sub

Cheers
Andy

 

[ellen s.]

Hi Andy,
I have no idea what that means. Could you somehow simply the language for
me, or at least show me where to start?

Thanks much!
Ellen

 

[Jon Peltier]

Here's an easier way. Hold Shift while you click on the chart. This
highlights the chart with white handles, not the usual black ones. On
the Format menu, select Object, and click on the Size tab. Check the
Lock Aspect Ratio box, then change 100% to 120% in either the Height or
Width Scale box.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Andy Pope]

Hi Jon,

I thought of that but this increases the chartobjects size. So chartarea
and plotarea increase. The code is the only systematic way I could think
of increasing just the plotarea.

Cheers
Andy

 

[Andy Pope]

Hi,

You need to place the code in a standard code module. From Excel
worksheet press ALT+F11 to open VBE (visual basic editor). You should
see a project window containing your workbook. Select this and use the
menu Insert > Module to add a empty code module to your workbook. Double
click Module1 and then paste the code into the open code module.
You can now switch back to the spreadsheet ALT+F11. Select the chart to
be resize and press ALT+F8 to display available Macros. X should be in
the list, select and click Run.

Depending on your current security settings you may need to change it to
Medium in order to run the actual macro. If you are doing this at your
place of work you may have to check with you IT department about
changing security levels. Any way to check use the menu Tools > Macro >
Security. Select Medium. Restart excel and it should work.

If you just want to increase the size of the chart object see Jon's
reply for a more simple solution.

Cheers
Andy

 

[Jon Peltier]

Sure, but the OP didn't know what VBA was, and after stretching the plot
area a few times, it's constrained by the chart area (chart object)
anyway. The trick is to make the plot area as large as possible within
the chart area, then stretch the chart object to suit.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Andy Pope]

Yeah, increasing the plotarea size will eventually be constrained by the
chartarea. And after seeing the OP's response, although I explain how to
use the VBA, changing the objects size certainly is the simpliest approach.

Cheers
Andy