Position charts apart

Posted on 2011-04-19
Last Modified: 2012-08-13
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

Question by:AndreasHermle
    LVL 85

    Expert Comment

    by:Rory Archibald
    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


    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
    LVL 85

    Accepted Solution

    Sorry - careless:

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

    Open in new window


    Author Closing Comment

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

    Regards, Andreas

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    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.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now