Andreas Hermle
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Regards, Andreas