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 - 27 Apr 2007 22:16 GMT
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
> Hi All,
>
[quoted text clipped - 6 lines]
> thanks,
> Navin
John Mansfield - 28 Apr 2007 06:28 GMT
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

Signature
John Mansfield
http://cellmatrix.net
> Hi All,
>
[quoted text clipped - 6 lines]
> thanks,
> Navin
Jon Peltier - 28 Apr 2007 19:55 GMT
John -
I was too lazy to go to this much effort in my post! Good job.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
> First be sure that your charts are named correctly. I use the following
> to
[quoted text clipped - 61 lines]
>> thanks,
>> Navin
John Mansfield - 30 Apr 2007 20:04 GMT
Thanks Jon. It was a good excercise in loading and manipulating arrays via
VBA.

Signature
John Mansfield
http://cellmatrix.net
> John -
>
[quoted text clipped - 72 lines]
> >> thanks,
> >> Navin