Sorting charts

[navin]

Hi All,

I want to sort the charts in a spreadsheet on its name and i have the
code to do this. but my code sorts the charts based on the index
value.

Is there any way to sort charts by there name.

thanks,
Navin

 

[Jon Peltier]

The index value is the Z order, where 1 is the one behind all the others,
and the highest number is in front of all the others. Adjust the Z order
(Bring Forward, Send Backward), then run your sorting routine. Or provide
the list of chart object names to the procedure and use these in order to
move each chart.

- Jon

 

[John Mansfield]

First be sure that your charts are named correctly. I use the following to
name charts:

Sub NameChart()
ActiveChart.Parent.Name = "AChart"
End Sub

Next, copy the code below to a regular module to sort your charts by name:

Sub SortChartNames()

Dim arrChartNames()
Dim Cht As ChartObject
Dim Buffer As Variant
Dim Rng As Range

X = 0

For Each Cht In ActiveSheet.ChartObjects
ReDim Preserve arrChartNames(X)
arrChartNames(X) = Cht.Name
X = X + 1
Next Cht

Buffer = Array_Sort(arrChartNames)

Z = 2

For Each X In Buffer
ActiveSheet.Shapes(X).Top = Z
ActiveSheet.Shapes(X).Left = 10
Z = Z + 90
Next X

End Sub


Private Function Array_Sort(ByVal arry As Variant) As Variant

Dim i As Long
Dim j As Long
Dim vElm As Variant

For i = LBound(arry) To UBound(arry)
For j = i + 1 To UBound(arry)
If arry(i) > arry(j) Then
vElm = arry(j)
arry(j) = arry(i)
arry(i) = vElm
End If
Next
Next
Array_Sort = arry
End Function

 

[Jon Peltier]

John -

I was too lazy to go to this much effort in my post! Good job.

- Jon

 

[John Mansfield]

Thanks Jon. It was a good excercise in loading and manipulating arrays via
VBA.
--
John Mansfield
http://cellmatrix.net