Positionin charts

[navin]

Hi All,

I have 15 charts in a sheet. I want to place these charts in a way
that the sheet contains, 3 charts in a row (i.e) total five columns.

any ideas how to achieve this.

thanks,
navin

 

[Andy Pope]

Hi,

Try this routine for positioning chart objects.
The spacing is determined by the widest/tallest objects.
There are 2 variables that can be set to control centering.

Sub PosCharts()

Dim sngLeft As Single
Dim sngTop As Single
Dim sngHeight As Single
Dim sngMaxWidth As Single
Dim sngMaxHeight As Single
Dim objCht As ChartObject
Dim lngIndex As Long
Dim blnHorizontalCenter As Boolean
Dim blnVerticalCenter As Boolean

For Each objCht In ActiveSheet.ChartObjects
If objCht.Width > sngMaxWidth Then sngMaxWidth = objCht.Width
If objCht.Height > sngMaxHeight Then sngMaxHeight = objCht.Height
Next

blnHorizontalCenter = True
blnVerticalCenter = True

sngTop = 1
For Each objCht In ActiveSheet.ChartObjects
lngIndex = lngIndex + 1
If lngIndex > 5 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1
sngLeft = 1
End If
If blnVerticalCenter Then
objCht.Top = sngTop + ((sngMaxHeight - objCht.Height) / 2)
Else
objCht.Top = sngTop
End If
If blnHorizontalCenter Then
objCht.Left = sngLeft + ((sngMaxWidth - objCht.Width) / 2)
Else
objCht.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1
Next

End Sub

Cheers
Andy

 

[Bernie Deitrick]

Navin,

Assuming that the only shapes that you have on the activesheet are the 15 charts, try the macro
below. See the in-line comments for use of the variables.

HTH,
Bernie
MS Excel MVP

Sub SpaceOutCharts()
Dim i As Integer
Dim j As Integer
Dim NumRow As Integer
Dim NumCol As Integer

Dim FirstRow As Integer
Dim FirstCol As Integer

Dim RowInc As Integer
Dim ColInc As Integer

'Specify 5 rows by 3 columns of charts
NumRow = 5
NumCol = 3

'Specify the cell for the first chart - C6 in this example
FirstRow = 6
FirstCol = 3

'Specify the spacing of the charts - these depend on your
'chart size and column width and row height - trial and error....
RowInc = 10
ColInc = 5

For i = 0 To NumRow - 1
For j = 0 To NumCol - 1
With ActiveSheet.Shapes(j * NumRow + i + 1)
.Left = Cells(i * RowInc + FirstRow, j * ColInc + FirstCol).Left
.Top = Cells(i * RowInc + FirstRow, j * ColInc + FirstCol).Top
End With
Next j
Next i

End Sub

 

[navin]

Thanks for the reply.

I am sorry that i didnt mention that, the sheet also has some data
from col A to D and from Column F to M.

so dargged the size of col E and positioned all the charts in that
with height of 253 and width of 386. so i thought of just adding the
top value of the first chart to the height which will give me the top
value for my next chart but this is not working.

can you tell me as how to adjust all the charts in the column E (row 1
to 97). Thanks for helping.

Regards,
Navin

 

[Bernie Deitrick]

Navin,

Sub SpaceOutCharts2()
Dim i As Integer
Dim NumCharts As Integer
Dim myTop As Integer
Dim myLeft As Integer
Dim TopInc As Integer
Dim myCell As Range

Set myCell = Range("E1")

'Find the number of charts
NumCharts = ActiveSheet.Shapes.Count

'Specify the cell for the first chart - E1 in this example
myTop = myCell.Top
myLeft = myCell.Left

'Specify the spacing of the charts
TopInc = 255

For i = 1 To NumCharts
With ActiveSheet.Shapes(i)
.Left = myLeft
.Top = myTop + TopInc * (i - 1)
End With
Next i

End Sub