Sorting by chart name
 

Hi All,

I have some 15 charts in a spreadsheet along with some data. I want to
reposition the charts in 3 rows and 5 col. I achieved this but the
issue, position of the charts get exchanged once i run the macro.

For example, after "Chart 4" is repositioned, macro selects
repositions "Chart 6" instead of "Chart 5". Below is the code i am
using:

sngMaxWidth = 386.25
sngMaxHeight = 255
sngLeft = 435
blnHorizontalCenter = True
blnVerticalCenter = True


sngTop = 1



For Each cht In ActiveSheet.ChartObjects
'For i = 1 To NumCharts

'With ActiveSheet.Shapes(i)
chname = cht.Name
With cht
lngIndex = lngIndex + 1
If lngIndex < 3 Then
blnVerticalCenter = False

End If

If lngIndex 3 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1 + 21
sngLeft = 435
End If
If blnVerticalCenter Then
.Top = sngTop + ((sngMaxHeight - .Height) / 2)
Else
.Top = sngTop
End If
If blnHorizontalCenter Then
.Left = sngLeft + ((sngMaxWidth - .Width) / 2)
Else
.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1 + 5
End With
'End If
Next cht

Am I missing something in the above code.

Thanks for the help.
Navin

Sorting by chart name
 

Navin,

You would need to have logic to decide which chart gets placed where.

Do you want to maintain the existing order, and just tidy things up a bit (like align the charts),
or do you want to have a specific order, no matter where the chart is originally placed, that is
based on chart name and not their index value?

HTH,
Bernie
MS Excel MVP


"navin" wrote in message
oups.com...
Hi All,

I have some 15 charts in a spreadsheet along with some data. I want to
reposition the charts in 3 rows and 5 col. I achieved this but the
issue, position of the charts get exchanged once i run the macro.

For example, after "Chart 4" is repositioned, macro selects
repositions "Chart 6" instead of "Chart 5". Below is the code i am
using:

sngMaxWidth = 386.25
sngMaxHeight = 255
sngLeft = 435
blnHorizontalCenter = True
blnVerticalCenter = True


sngTop = 1



For Each cht In ActiveSheet.ChartObjects
'For i = 1 To NumCharts

'With ActiveSheet.Shapes(i)
chname = cht.Name
With cht
lngIndex = lngIndex + 1
If lngIndex < 3 Then
blnVerticalCenter = False

End If

If lngIndex 3 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1 + 21
sngLeft = 435
End If
If blnVerticalCenter Then
.Top = sngTop + ((sngMaxHeight - .Height) / 2)
Else
.Top = sngTop
End If
If blnHorizontalCenter Then
.Left = sngLeft + ((sngMaxWidth - .Width) / 2)
Else
.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1 + 5
End With
'End If
Next cht

Am I missing something in the above code.

Thanks for the help.
Navin

Sorting by chart name
 

Thanks for the reply.

Yes. I want to keep the existing order of the charts and align the
charts.

Thanks,
Navin


Bernie Deitrick wrote:
Navin,

You would need to have logic to decide which chart gets placed where.

Do you want to maintain the existing order, and just tidy things up a bit (like align the charts),
or do you want to have a specific order, no matter where the chart is originally placed, that is
based on chart name and not their index value?

HTH,
Bernie
MS Excel MVP


"navin" wrote in message
oups.com...
Hi All,

I have some 15 charts in a spreadsheet along with some data. I want to
reposition the charts in 3 rows and 5 col. I achieved this but the
issue, position of the charts get exchanged once i run the macro.

For example, after "Chart 4" is repositioned, macro selects
repositions "Chart 6" instead of "Chart 5". Below is the code i am
using:

sngMaxWidth = 386.25
sngMaxHeight = 255
sngLeft = 435
blnHorizontalCenter = True
blnVerticalCenter = True


sngTop = 1



For Each cht In ActiveSheet.ChartObjects
'For i = 1 To NumCharts

'With ActiveSheet.Shapes(i)
chname = cht.Name
With cht
lngIndex = lngIndex + 1
If lngIndex < 3 Then
blnVerticalCenter = False

End If

If lngIndex 3 Then ' Number of columns
lngIndex = 1
sngTop = sngTop + sngMaxHeight + 1 + 21
sngLeft = 435
End If
If blnVerticalCenter Then
.Top = sngTop + ((sngMaxHeight - .Height) / 2)
Else
.Top = sngTop
End If
If blnHorizontalCenter Then
.Left = sngLeft + ((sngMaxWidth - .Width) / 2)
Else
.Left = sngLeft
End If
sngLeft = sngLeft + sngMaxWidth + 1 + 5
End With
'End If
Next cht

Am I missing something in the above code.

Thanks for the help.
Navin