View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Bernie Deitrick Bernie Deitrick is offline
external usenet poster
 
Posts: 5,441
Default 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