Solved

# Position charts apart

Posted on 2011-04-19
400 Views
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
Question by:AndreasHermle

LVL 85

Expert Comment

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

Author Comment

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

LVL 85

Accepted Solution

Sorry - careless:

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

Author Closing Comment

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

Regards, Andreas
0

## Featured Post

### Suggested Solutions

Excel file corrupted. 13 29
Running Out of IP Addresses 9 95
recovering Excel 2016 file 2 24
Excel totals by filter - Automated 4 14
A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.