Log In
Register
Home
February 27, 2020
Offline
Hi
I am using the Excel 2013
I have to change in select data the Specify Order For Series collection in Select Data for Chart
=SERIES([Series Name],[X Values],[Y Values],[Plot Order])
I have to change the "Holder" series collection from 5th to 1st move
I actually did something similar. I put "ActiveChart.SeriesCollection(5).PlotOrder = 1" for the first data series, but it doesn't work that way.
when i run the vba code "ActiveChart.SeriesCollection(5).PlotOrder = 1", but this go 3rd position the "Holder".
I don't know, what I am wrong my VBA code... but not working
We need that the "Holder" series move from 5th to 1st position (Untitled image and VBA code Excel enclosed for your reference)
Kindly correction the VBA code
Thanks for Help....
February 20, 2020
Offline
Hello,
If you could load a workbook with a sample, it would be much easier, but I will try to help
a lot of data is missing about how data is organized in your spreadsheet, how it is obtained and what type of chart you are using example:
1 - assuming you have the data on the sheet and a command button on the sheet (that is, you don't use a user form to display the graph)
2 - I suppose it has only 5 series
3 - I used this type of graph ( xlColumnClustered )
' place the following procedure on the sheet module
Private Sub CommandButton1_Click()
Dim myChartObj As ChartObject Dim i As Long Dim NewChartOrder As Variant ' will be for the new series order Dim OldChartOrder(0 To 4) As String ' if you have 5 series, you will have to write like this
Application.ThisWorkbook.Worksheets("Sheet1").Activate
If Not ChartExists(Application.ThisWorkbook.Worksheets("Sheet1"), "MyChartName") Then
' Chart not exist
Exit Sub
End If
Set myChartObj = Application.ThisWorkbook.Worksheets("Sheet1").ChartObjects("MyChartName")
myChartObj.Activate
' MsgBox ActiveChart.SeriesCollection.Count ' if you want to know how many series your chart has
NewChartOrder = Array(5, 1, 2, 3, 4) ' this will be the order of the series, each time you click the command button, series 5 moves back one position
' if you want to have the order immediately of 5,1,2,3,4 activate the following line of code and disable the previous one to this information
' NewChartOrder = Array(2, 3, 4, 5, 1)
With ActiveChart
For i = LBound(NewChartOrder) To UBound(NewChartOrder)
OldChartOrder(i) = .SeriesCollection(i + 1).Name
Next i
For i = LBound(NewChartOrder) To UBound(NewChartOrder)
.SeriesCollection(OldChartOrder(i)).PlotOrder = NewChartOrder(i)
Next i
End With
If Not myChartObj Is Nothing Then Set myChartObj = Nothing
End Sub
' place the following procedure on standart module
Public Function ChartExists(wsTest As Worksheet, strChartName As String) As Boolean ' chart name exist ?
Dim chTest As ChartObject
On Error Resume Next
Set chTest = wsTest.ChartObjects(strChartName)
On Error GoTo 0
If chTest Is Nothing Then
ChartExists = False
Else
ChartExists = True
End If
End Function
Regards,
Miguel
1 Guest(s)
