Chart Plot Area Size

Chris84

Board Regular
Joined
Aug 17, 2012
Messages
78
Hi

I have some code which creates 3 bar charts
1st Chart % between 40 and 120
2nd Chart count between 8 and 100
3rd Chart count between 200 and 5000
the code makes the charts identical in size in some instances this works great and makes the data easy to read.

Normally what happens is Chart 2's Vertical (Value) Axis is narrower than the other charts which makes the plot area wider.

Is there a way to set the plot area size of the bar chart without this affecting the axis size, alternatively can I change the Vertical (Value) Axis width.

The code i'm using for the width is

Code:
    ActiveChart.PlotArea.Select
    Selection.Left = 20
    Selection.Width = 490

but this includes the axis

Any help is greatly appreciated
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
If you turn off the Font Autoscale feature before changing the plot area size, the axis width should not change.

Code:
    [COLOR=darkblue]With[/COLOR] ActiveChart
        [COLOR=#ff0000].Axes(xlValue).TickLabels.AutoScaleFont = False[/COLOR]
        .PlotArea.Left = 20
        .PlotArea.Width = 490
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Or set the font size to a specific size to set the axis width.
 
Last edited:
Upvote 0
Hi AlphaFrog

Sadly didn't work still scaling the plot area with the axis.

When I use the macro recorder to rescale the plot area it records just the plot area but when you run the code again it unclouded the axis.

Thanks
 
Upvote 0
When I use the macro recorder to rescale the plot area it records just the plot area but when you run the code again it unclouded the axis.

Then I don't follow what you are asking. Unclouded?

What version of Excel do you have?
 
Upvote 0
Hi AlphaFrog

Sorry that it's not clear, Unfortunatly it's not clear in my head.

I need to either lock the plot area or change the axle values width.

I have just tried this

Code:
With activechart
   .Axes(xlValue).MaximumScale = 1000
End With

Which gives me the correct width to the other 2 charts.

So hopefully my question is could I set the maximum scale to say 1000, lock the Axes width then set maximumScaleIsAuto = true so it will revert back to highest value.

Thanks again
 
Upvote 0
Do you want to change the plot area size but maintain the y-axis spacing of each number so it matches the other charts? So the top value on the y axis would increase with the plot area, but the physical spacing of each value remains the same. Is that correct?
 
Upvote 0
Hi AlphaFrog

Sorry once again

I want the plot area where the graph is to stay the same size in all three charts. Ideally I want the plot area to start at left = 60 and the Axel to be left = 20.

All three charts have the same customer names on the horizontal axis but provide different data so when you look at the sheet you can quickly look down without having to read the horizontal axis, but because chart 2 has a different width vertical axis is throws the plot area out.

I have now found something that works but does not look very professional as it will never have decimal places

Code:
    With ActiveChart
        .Axes(xlValue).TickLabels.AutoScaleFont = False
        .Axes(xlValue).TickLabels.NumberFormat = "#,##0.00"
        .PlotArea.Left = 20
        .PlotArea.Width = 490
    End With

Thanks
 
Upvote 0
Try something like this...

.Axes(xlValue).TickLabels.NumberFormat = " 0"

Add\Remove spaces in front of the 0 to adjust the width.


Or something like this...
Code:
    l& = 20
    [COLOR=darkblue]With[/COLOR] ActiveChart.PlotArea
        .Width = .Width + .Left - l
        .Left = l
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
Upvote 0
Hi AlphaFrog

Thanks so much I used .Axes(xlValue).TickLabels.NumberFormat = " 0" with six spaces and it looks great.
 
Upvote 0
You're welcome. Note: That method is imperfect as the number of spaces will vary depending on the width of the widest value on the y axis. But if that works for you then great.

This will calculate how much to move left the plot area if the y axis values had 4 digits. Test it on a new chart.
Code:
    [COLOR=darkblue]With[/COLOR] ActiveChart
        .Axes(xlValue).TickLabels.NumberFormat = "0000"
        w# = .PlotArea.InsideWidth
        .Axes(xlValue).TickLabels.NumberFormat = "0"
        [COLOR=darkblue]With[/COLOR] .PlotArea
            w = .InsideWidth - w
            .Width = .Width - w
            .Left = .Left + w
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,213,582
Messages
6,114,468
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top