Sorting charts
 

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

Sorting charts
 

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
_______


"navin" wrote in message
oups.com...
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

Sorting charts
 

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

--
John Mansfield
http://cellmatrix.net





"navin" wrote:

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

Sorting charts
 

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
_______


"John Mansfield" wrote in message
...
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

--
John Mansfield
http://cellmatrix.net





"navin" wrote:

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

Sorting charts
 

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





"Jon Peltier" wrote:

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
_______


"John Mansfield" wrote in message
...
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

--
John Mansfield
http://cellmatrix.net





"navin" wrote:

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