Thread: How to resize plot areas so that graphs are uniform?

Do the charts have the same x and y axis labelling and how about the legend text?

The plot area will be adjusted automatically to allow for variations in the size/amount of text.

To force the sizing of the plotarea you would need code to loop through and adjust it's width/height/top/left.

The code to loop and change is straight forward. What is not is what values to use. How do you know which dimensions should be used on all the charts?

Originally Posted by Andy Pope

Do the charts have the same x and y axis labelling and how about the legend text?

The plot area will be adjusted automatically to allow for variations in the size/amount of text.

To force the sizing of the plotarea you would need code to loop through and adjust it's width/height/top/left.

The code to loop and change is straight forward. What is not is what values to use. How do you know which dimensions should be used on all the charts?

Thanks for the reply Andy. Almost all the charts have different x and y labels, also the legend position and contents varies considerably. I think I must have manually edited some of the plot areas at some point because they do not necessaraly match for otherwise identical charts. e.g:

Example.

These two empty graphs have the same dimensions when you select them, but the plot areas do not match.

There is no exact answer as to what the plot area dimensions should be, but the outside dimensions are 7.5cm by 15.92cm. So essentially they just need to be uniform and fill most of the width of one sheet of A4 paper (15.92cm + 2x2.54cm margins) and most of the 7.5cm chart height. How would I go about writing the code?

Thanks,

SB

are the charts in excel or word.

Can you post a file rather than a image.

This is a cheap and cheerful fix

Code:

Sub X()
    
    Dim objInLine As InlineShape
    Dim chtTemp As Chart
    Dim shpTemp As Shape
    
    For Each objInLine In ActiveDocument.InlineShapes
        If objInLine.HasChart Then
            Set chtTemp = objInLine.Chart
            With chtTemp.PlotArea
                .Width = chtTemp.ChartArea.Width * 0.8
                .Left = chtTemp.ChartArea.Width * 0.1
                .Height = chtTemp.ChartArea.Height * 0.8
                .Top = chtTemp.ChartArea.Height * 0.1
            End With
        End If
    Next
    
End Sub

Thanks for that Andy, your help is much appreciated. It works well, though I had trouble with one or two for some reason. Is there any way to make it so that it just resizes the currently selected graph?

I can not get at the chart object via the Selection object.
Nor can I identify what the selected item is called.

If the code is missing some charts it maybe because they are in the shapes collection rather the InLineShapes collection.

Thanks Andy. I suppose I could open a new word document and transfer the charts I want resizing and run the macro on those and then transfer them back. It certainly beats doing it manually with the ruler guides!!

I took a break and had another bash.

Try this on a selected chart

Code:

Sub X()
    
    Dim objInLine As InlineShape
    Dim chtTemp As Chart
    
    Set objInLine = ActiveWindow.Selection.InlineShapes(1)
    If Not objInLine Is Nothing Then
        If objInLine.HasChart Then
            Set chtTemp = objInLine.Chart
            With chtTemp.PlotArea
                .Width = chtTemp.ChartArea.Width * 0.8
                .Left = chtTemp.ChartArea.Width * 0.1
                .Height = chtTemp.ChartArea.Height * 0.8
                .Top = chtTemp.ChartArea.Height * 0.1
            End With
        End If
    End If
    
End Sub

Works perfectly, thanks! I figured out why a couple of them didn't resize 'correctly', it was because I had forgotten to change the font size of the y-axis to the same as the others, it made the end result slightly different but I have changed that now. Cheers Andy, the help has been massively appreciated!

Silverbrook, kindly don't quote whole posts. It's just clutter.

Thanks.

You just need to add the code to control the right part of the charts object model.

Code:

Sub X()
    
    Dim objInLine As InlineShape
    Dim chtTemp As Chart
    
    Set objInLine = ActiveWindow.Selection.InlineShapes(1)
    If Not objInLine Is Nothing Then
        If objInLine.HasChart Then
            Set chtTemp = objInLine.Chart
            With chtTemp.PlotArea
                .Width = chtTemp.ChartArea.Width * 0.8
                .Left = chtTemp.ChartArea.Width * 0.1
                .Height = chtTemp.ChartArea.Height * 0.8
                .Top = chtTemp.ChartArea.Height * 0.1
            End With
            If chtTemp.HasLegend Then
                With chtTemp.Legend
                    .Left = 10
                    .Top = 10
                    .Width = 50
                    .Height = 50
                End With
            End If
' X axis
            If chtTemp.Axes(1, 1).HasTitle Then
                chtTemp.Axes(1, 1).AxisTitle.Left = chtTemp.PlotArea.InsideLeft
            End If
' Y axis            
            If chtTemp.Axes(2, 1).HasTitle Then
                chtTemp.Axes(2, 1).AxisTitle.Top = chtTemp.PlotArea.InsideTop
            End If
            
        End If
    End If
    
End Sub