Resize charts based on active chart

Dear Experts:

below macro (it is really a fantastic one) ...
... resizes all charts on the active worksheet so that they match
the dimensions of the active chart.
... It also arranges the charts into a user specified
number of columns.

Could somebody help me tweak this code so that ...
... only charts named 'MyCharts_##' are worked on.  (The number sign ## stands for 01, 02, 03, ... 12, 13 etc.)
The number of columns into which the charts get arranged should be fixed to 3 columns.

Help is very 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

Andreas HermleTeam leaderAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Andreas,
I revised the macro to only align charts in a grid if they were named like MyCharts_xx, but did not test it. If there is any problem with the code, could you please post a sample workbook so I can test it?
Sub SizeAndAlignCharts()
Dim W As Long, H As Long
Dim TopPosition As Long, LeftPosition As Long
Dim ChtObj As ChartObject
Dim i As Long, j 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
    If ActiveSheet.ChartObjects(i).Name Like "MyCharts_##" Then
        j = j + 1
        With ActiveSheet.ChartObjects(i)
            .Width = W
            .Height = H
            .Left = LeftPosition + ((j - 1) Mod NumCols) * W
            .Top = TopPosition + Int((j - 1) / NumCols) * H
        End With
    End If
Next i
End Sub

Open in new window

Brad
0
 
Andreas HermleTeam leaderAuthor Commented:
As always, this works like a charm. Thank you very much for your professional help.

Regards, Andreas
0
All Courses

From novice to tech pro — start learning today.