Sorting chart by name

[navin]

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

 

[Barb Reinhardt]

Are you sure it's choosing the chart that's designated as "Chart 6" by Excel,
or a chart that you call "Chart 6"?

 

[navin]

i checked, actually it takes "Chart 6" designated by excel and not
chart name "Chart 6"

Please help.

Thanks,
navin

 

[Barb Reinhardt]

You can do this one of two ways.

1 - Look at the title of the chart and sort based upon that
2 - Rename the chart names.

Given what you know about this, which is better for you to do.

 

[navin]

Can you tell me how to sort charts based on the title.

Thanks,
Navin

 

[Barb Reinhardt]

Unfortunately, I don't have time to go through your code and figure out where
this goes.

This snippet of code should provide enough information for you to be able to
extract the chart title.

Dim aWS As Worksheet
Dim chtObj As ChartObject
For Each aWS In ActiveWorkbook.Worksheets
Debug.Print aWS.Name
For Each chtObj In aWS.ChartObjects
If chtObj.Chart.HasTitle Then
Debug.Print chtObj.Chart.ChartTitle.Text
End If
Next chtObj
Next aWS