Sorting charts

  • Thread starter Thread starter navin
  • Start date Start date
N

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
 
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
 
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
 
Back
Top