• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Designing all Charts

Hi shay, try this one...

Code:
Sub Resizeallcharts()

Dim chart As ChartObject

For Each chart In ActiveSheet.ChartObjects
    With chart.Parent
        chart.Height = Application.InchesToPoints(2.5)
        chart.Width = Application.InchesToPoints(5)
    End With
Next

End Sub

You can change size (vertical or horizontal) by changing the numbers in "Chart.height" and "chart.width"
 
Or without using VBA
Set the Rows and Columns behind the charts to be the same size (the width doesn't have to be the same as the height)
Then whilst holding the Alt Key down, drag the corners to the nearest intersections of Rows/Columns
Repeat the dragging for all charts
 
Thank you both!
About the VBS solution, is it possible to incorporate Input Box to determine desirable width and height?
Regarding the second solution, could you please elaborate a bit more about using the ALT key while moving an obje? Should this action locate the chart EXACTLY pn the gridlinrs? And in general, what is the right way to use the Alt key whild moving around a chart?

Many thanks
 
If you drag the handles of an object (the small circles and squares shown below)
upload_2016-11-8_10-11-29.png
you will move the edges and corners as appropriate

If you hold down the Alt key as you do that you will notice that the edges & corners will snap to adjacent gridlines

This enables precise sizing of all similar placed objects

Another advantage of this is that if you change for example the Row Height any objects snapped to that Row will also change size as if they are glued to that row's edge

You can move the object, If you click and drag the centre of the object
If You hold down the Alt key as that happens, you will see it jump to the nearest row/column or intersection as appropriate
 
Code:
Sub Resizeallcharts()

Dim chart As ChartObject
Dim xSize As Double, ySize As Double

xSize = Application.InputBox("Please enter Horizontal size", "Horizontal Size", Type:=1)
ySize = Application.InputBox("Please enter Vertical size", "Vertical Size", Type:=1)

For Each chart In ActiveSheet.ChartObjects
  With chart.Parent
  chart.Height = Application.InchesToPoints(ySize)
  chart.Width = Application.InchesToPoints(xSize)
  End With
Next

End Sub
 
Back
Top