Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Chart Plot Area Size

This is a discussion on Chart Plot Area Size within the Excel Questions forums, part of the Question Forums category; Hi I have some code which creates 3 bar charts 1st Chart % between 40 and 120 2nd Chart count ...

  1. #1
    Board Regular
    Join Date
    Aug 2012
    Posts
    76

    Default Chart Plot Area Size

    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

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,011

    Default Re: Chart Plot Area Size

    If you turn off the Font Autoscale feature before changing the plot area size, the axis width should not change.

    Code:
        With ActiveChart
            .Axes(xlValue).TickLabels.AutoScaleFont = False
            .PlotArea.Left = 20
            .PlotArea.Width = 490
        End With
    Or set the font size to a specific size to set the axis width.
    Last edited by AlphaFrog; Oct 14th, 2012 at 12:54 PM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  3. #3
    Board Regular
    Join Date
    Aug 2012
    Posts
    76

    Default Re: Chart Plot Area Size

    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

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,011

    Default Re: Chart Plot Area Size

    Quote Originally Posted by Chris84 View Post
    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?
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  5. #5
    Board Regular
    Join Date
    Aug 2012
    Posts
    76

    Default Re: Chart Plot Area Size

    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

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,011

    Default Re: Chart Plot Area Size

    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?
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  7. #7
    Board Regular
    Join Date
    Aug 2012
    Posts
    76

    Default Re: Chart Plot Area Size

    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

  8. #8
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,011

    Default Re: Chart Plot Area Size

    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
        With ActiveChart.PlotArea
            .Width = .Width + .Left - l
            .Left = l
        End With
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  9. #9
    Board Regular
    Join Date
    Aug 2012
    Posts
    76

    Default Re: Chart Plot Area Size

    Hi AlphaFrog

    Thanks so much I used .Axes(xlValue).TickLabels.NumberFormat = " 0" with six spaces and it looks great.

  10. #10
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    12,011

    Default Re: Chart Plot Area Size

    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:
        With ActiveChart
            .Axes(xlValue).TickLabels.NumberFormat = "0000"
            w# = .PlotArea.InsideWidth
            .Axes(xlValue).TickLabels.NumberFormat = "0"
            With .PlotArea
                w = .InsideWidth - w
                .Width = .Width - w
                .Left = .Left + w
            End With
        End With
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com