• Status: Solved
• Priority: Medium
• Security: Public
• Views: 451

# Position charts apart

Dear Experts:

The following code  example (courtesy by John Walkenbach) ...
... resizes all ChartObject objects on a sheet so that they match
the dimensions of the active chart.
.... It also arranges the ChartObject objects into a user specified number of columns.
....If no chart is active, the user is prompted to activate a chart that will be used as the basis
for sizing the other charts.

The code places the charts either on top of each other or arranges them in n-columns.

I wonder whether it is possible  to tweak below code so that ...
.... the charts are, say 10 points apart (vertically and horizontally)

I attached a sample file for your convenience

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

``````Sub SizeAndAlignCharts()
Dim W As Long, H As Long
Dim TopPosition As Long, LeftPosition As Long
Dim ChtObj As ChartObject
Dim i As Long, NumCols As Long
If ActiveChart Is Nothing Then
MsgBox “Select a chart to be used as the base for the sizing”
Exit Sub
End If
‘Get columns
On Error Resume Next

NumCols = InputBox(“How many columns of charts?”)
If Err.Number <> 0 Then Exit Sub
If NumCols < 1 Then Exit Sub
On Error GoTo 0
‘Get size of active chart
W = ActiveChart.Parent.Width
H = ActiveChart.Parent.Height
‘Change starting positions, if necessary
TopPosition = 100
LeftPosition = 20
For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
.Width = W
.Height = H
.Left = LeftPosition + ((i - 1) Mod NumCols) * W
.Top = TopPosition + Int((i - 1) / NumCols) * H
End With
Next i
End Sub
``````
ResizeCharts.xlsm
0
AndreasHermle
• 2
• 2
1 Solution

Commented:
Change lines 27 and 28 to:
``````.Left = LeftPosition + ((i - 1) Mod NumCols) * W + 10
.Top = TopPosition + Int((i - 1) / NumCols) * H + 10
``````
0

Author Commented:
Hi rorya:
thank  you very much for your  swift support.

That's what I also tried at the beginning. But regrettably with no effect whatsoever. Any idea why?

Regards, Andreas
0

Commented:
Sorry - careless:

``````.Left = LeftPosition + ((i - 1) Mod NumCols) * (W + 10)
.Top = TopPosition + Int((i - 1) / NumCols) * (H + 10)
``````
0

Author Commented:
Great rorya, that's it. Thank you  very much for  your  ever professional and swift support.

Regards, Andreas
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.