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

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

Open in new window

  • 2
  • 2
1 Solution
Rory ArchibaldCommented:
Change lines 27 and 28 to:
.Left = LeftPosition + ((i - 1) Mod NumCols) * W + 10
.Top = TopPosition + Int((i - 1) / NumCols) * H + 10

Open in new window

AndreasHermleAuthor 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
Rory ArchibaldCommented:
Sorry - careless:

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

Open in new window

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

Regards, Andreas

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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