Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Charting / April 2007

Tip: Looking for answers? Try searching our database.

Sorting charts

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
navin - 27 Apr 2007 14:27 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.