Forum Discussion

Mike Curnow's avatar
Mike Curnow
Copper Contributor
Aug 24, 2018
Solved

How to Resize Excel Plot Area

Hi, I have a Bubble Chart where I am plotting a value (Z) against X/Y co-ordinates. I need to make the X & Y axis the same scale, i.e. I need to size the Plot Area so that the co-ordinates map onto ...
Charting
  • Philip West's avatar
    Philip West
    Aug 24, 2018
    Sub plotplot()
    Dim w As Variant
    Dim h As Variant
    Dim crt_name As Variant

    w = InputBox("Width in cm")
    h = InputBox("Height in cm")
    crt_name = InputBox("Chart name")

        ActiveSheet.ChartObjects(crt_name).Activate
        ActiveChart.PlotArea.Select
        Selection.Width = Application.CentimetersToPoints(w)
        Selection.Height = Application.CentimetersToPoints(h)

    End Sub

    This should do what you want.. if you add it as module and run it you should be asked for 3 things.. width in cm, height in cm and the chart name. You should be able to find the chart name by clicking it and the going to the Chart tools->layout ribbon. Default is Chart 1, Chart 2 etc.

     

    Normal warnings, make sure you have a saved copy of your workbook before trying this, you cant undo actions taken by VBA..

     

    Hope it does what you want.

     

     

Philip West's avatar
Philip West
Iron Contributor
Aug 24, 2018
Sub plotplot()
Dim w As Variant
Dim h As Variant
Dim crt_name As Variant

w = InputBox("Width in cm")
h = InputBox("Height in cm")
crt_name = InputBox("Chart name")

    ActiveSheet.ChartObjects(crt_name).Activate
    ActiveChart.PlotArea.Select
    Selection.Width = Application.CentimetersToPoints(w)
    Selection.Height = Application.CentimetersToPoints(h)

End Sub

This should do what you want.. if you add it as module and run it you should be asked for 3 things.. width in cm, height in cm and the chart name. You should be able to find the chart name by clicking it and the going to the Chart tools->layout ribbon. Default is Chart 1, Chart 2 etc.

 

Normal warnings, make sure you have a saved copy of your workbook before trying this, you cant undo actions taken by VBA..

 

Hope it does what you want.

 

 

Cordillera94's avatar
Cordillera94
Copper Contributor
Nov 26, 2021

Philip West 

Hello Philip, not sure if you're still around but I've encountered a problem with this.
I am making several charts, the Y and X axis need to be the same scale on each chart, and also the different charts need to be the same scale. 

However, if the charts are not the same absolute size, the scales are not the same.

See the attached image for an example. Using your method, I set the height of both plots to 4cm. The width of Example 1 I set to 15 cm, and the width of Example 2 I set to double at 30 cm. Since the values of the X axis of Example 2 were also doubled, the scales of the X axis should be the same. However, as you can see they do not line up. Any solutions?

Resources