VBA Modifying charts: Adding a new series

MacGyver7640

Board Regular
Joined
Oct 28, 2011
Messages
76
Hi, trying to debug this code I'm working on to add two series to a chart.

Couple of questions:

1) Most of the charts I want to modify have 5 series, so adding a 6th and 7th works. But some have only 4 and some just 1. How might I add two additional new series, rather than a 6th and 7th? If I try to apply this code to a chart with fewer than 5 series, it just deletes the chart. (Sidenote: Why is that?)

2) Right now it applies to ChartObjects(1). How do I get it to work on the next chart after it finishes with the first? I want all line charts on the sheet to be modified. I was trying:

Dim ch As ChartObject
Sub AllCharts()
For Each ch In ActiveSheet.ChartObjects
If ch.Chart.ChartType <> xlColumnStacked Then _


but it did not work

Code:
Sub AddEventsv2()

On Error Resume Next
'Create Series: Event 1, change to scatterplot, Scale secondary vertical axis 0 to 1, delete legend entry for Event 1
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).Name = "=""Event 1"""
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection(6).Select
    ActiveChart.SeriesCollection(6).AxisGroup = 2
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection(6).Select
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.SeriesCollection(6).ChartType = xlXYScatterLines
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.Legend.LegendEntries(6).Select
    Selection.Delete
    ActiveSheet.ChartObjects(1).Activate
    
    'Data for Event 1
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(6).XValues = "='Intructions'!$Z$3:$Z$4"
    ActiveChart.SeriesCollection(6).Values = "='Intructions'!$AA$3:$AA$4"

    
'Create Series: Event 2, Scale secondary vertical axis 0 to 1, delete legend entry for Event 2
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(7).Name = "=""Event 2"""
    
    'Data for Event 2
    ActiveChart.SeriesCollection(7).XValues = "='Intructions'!$Z$5:$Z$6"
    ActiveChart.SeriesCollection(7).Values = "='Intructions'!$AA$5:$AA$6"
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlValue, xlSecondary).Select
    ActiveChart.SeriesCollection(7).ChartType = xlXYScatterLines
    ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 1
    ActiveChart.SeriesCollection(6).Select
    Selection.MarkerStyle = -4142
    ActiveChart.SeriesCollection(7).Select
    Selection.MarkerStyle = -4142
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.Axes(xlValue, xlSecondary).Select
    Selection.TickLabelPosition = xlNone
    ActiveChart.Axes(xlValue, xlSecondary).Select
    Selection.MajorTickMark = xlNone
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.Legend.LegendEntries(6).Select
    Selection.Delete
    
'Format Vertical Lines, format to black, dashed, weight 1.5
    ActiveChart.SeriesCollection(6).Select
    ActiveChart.SeriesCollection(6).Format.Line.Weight = 1.5
    ActiveChart.SeriesCollection(6).Format.Line.Visible = msoCTrue
    ActiveChart.SeriesCollection(6).Format.Line.ForeColor.RGB = vbBlack
    ActiveChart.SeriesCollection(6).Format.Line.DashStyle = msoLineSysDash
    ActiveChart.SeriesCollection(7).Select
    ActiveChart.SeriesCollection(7).Format.Line.Weight = 1.5
    ActiveChart.SeriesCollection(7).Format.Line.Visible = msoCTrue
    ActiveChart.SeriesCollection(7).Format.Line.ForeColor.RGB = vbBlack
    ActiveChart.SeriesCollection(7).Format.Line.DashStyle = msoLineSysDash


End Sub

Thank you guys so much! I apologize if my code is messy, just learning VBA :)
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

MacGyver7640

Board Regular
Joined
Oct 28, 2011
Messages
76
Figured out the second question thanks for Worf! (I think it was the underscore that was throwing me off)

Still wondering about the first question...How add a series without knowing how many series already exist. (If 3 series add a fourth...etc) because as I said, it is deleted the chart and a cell (oddly) when there isn't exactly 5 existing series.

Code:
Sub AddEventsv2()
Dim ch As ChartObject

On Error Resume Next

For Each ch In ActiveSheet.ChartObjects
'changes only charts that are line charts
If ch.Chart.ChartType = xlLine Then
        
'Create Series: Event 1, change to scatterplot, Scale secondary vertical axis 0 to 1, delete legend entry for Event 1
    ch.Chart.ChartObjects(1).Activate
    ch.Chart.SeriesCollection.NewSeries
    ch.Chart.SeriesCollection(6).Name = "=""Event 1"""
    ch.Chart.ChartObjects(1).Activate
    ch.Chart.SeriesCollection(6).Select
    ch.Chart.SeriesCollection(6).AxisGroup = 2
    ch.Chart.ChartObjects(1).Activate
    ch.Chart.SeriesCollection(6).Select
    ch.Chart.ChartObjects(1).Activate
    ch.Chart.SeriesCollection(6).ChartType = xlXYScatterLines
    ch.Chart.ChartObjects(1).Activate
    ch.Chart.Legend.LegendEntries(6).Select
    Selection.Delete
    ch.Chart.ChartObjects(1).Activate
    
    'Data for Event 1
    ch.Chart.PlotArea.Select
    ch.Chart.SeriesCollection(6).XValues = "='Intructions'!$Z$3:$Z$4"
    ch.Chart.SeriesCollection(6).Values = "='Intructions'!$AA$3:$AA$4"

    
'Create Series: Event 2, Scale secondary vertical axis 0 to 1, delete legend entry for Event 2
    ch.Chart.ChartArea.Select
    ch.Chart.SeriesCollection.NewSeries
    ch.Chart.SeriesCollection(7).Name = "=""Event 2"""
    
    'Data for Event 2
    ch.Chart.SeriesCollection(7).XValues = "='Intructions'!$Z$5:$Z$6"
    ch.Chart.SeriesCollection(7).Values = "='Intructions'!$AA$5:$AA$6"
    ch.Chart.ChartArea.Select
    ch.Chart.Axes(xlValue, xlSecondary).Select
    ch.Chart.SeriesCollection(7).ChartType = xlXYScatterLines
    ch.Chart.Axes(xlValue, xlSecondary).MaximumScale = 1
    ch.Chart.SeriesCollection(6).Select
    Selection.MarkerStyle = -4142
    ch.Chart.SeriesCollection(7).Select
    Selection.MarkerStyle = -4142
    ch.Chart.Axes(xlValue).MajorGridlines.Select
    ch.Chart.ChartObjects(1).Activate
    ch.Chart.Axes(xlValue, xlSecondary).Select
    Selection.TickLabelPosition = xlNone
    ch.Chart.Axes(xlValue, xlSecondary).Select
    Selection.MajorTickMark = xlNone
    ch.Chart.ChartObjects(1).Activate
    ch.Chart.Legend.LegendEntries(6).Select
    Selection.Delete
    
'Format Vertical Lines, format to black, dashed, weight 1.5
    ch.Chart.SeriesCollection(6).Select
    ch.Chart.SeriesCollection(6).Format.Line.Weight = 1.5
    ch.Chart.SeriesCollection(6).Format.Line.Visible = msoCTrue
    ch.Chart.SeriesCollection(6).Format.Line.ForeColor.RGB = vbBlack
    ch.Chart.SeriesCollection(6).Format.Line.DashStyle = msoLineSysDash
    ch.Chart.SeriesCollection(7).Select
    ch.Chart.SeriesCollection(7).Format.Line.Weight = 1.5
    ch.Chart.SeriesCollection(7).Format.Line.Visible = msoCTrue
    ch.Chart.SeriesCollection(7).Format.Line.ForeColor.RGB = vbBlack
    ch.Chart.SeriesCollection(7).Format.Line.DashStyle = msoLineSysDash

    End If
Next

End Sub
 

MacGyver7640

Board Regular
Joined
Oct 28, 2011
Messages
76
When all of the chart only have 4 series, I can change 6 to 5 and 7 to 6 (Ex: ch.Chart.SeriesCollection(6).Select to ch.Chart.SeriesCollection(5).Select) which works perfectly. I'm doing this manally becuase I don't know a way to just add an additional series, regardless of which one it is (the 5th or the 6th series on that chart).

But when I have just one series when change the 6 to 2 and the 7 to 3...all it does it delete the grid lines and add a series called "Series 2" with no value. I am quite perplexed.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi,

Jon Peltier's site describes some techniques that would address the challenges you are finding. Look under the section titled "Add a Series". In particular, he shows how you can set a Series object when you create a new series. This will allow you to edit the new series' properties by its object instead of its SeriesCollection Item number.

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

On that same page, Jon shows ways in which you can take code that was recorded by the macro recorder and make it more streamlined and efficient.
Using just a few techniques you will be able to cut the number of line of code you have by 50% or more.
 

MacGyver7640

Board Regular
Joined
Oct 28, 2011
Messages
76

ADVERTISEMENT

Hi,

Jon Peltier's site describes some techniques that would address the challenges you are finding. Look under the section titled "Add a Series". In particular, he shows how you can set a Series object when you create a new series. This will allow you to edit the new series' properties by its object instead of its SeriesCollection Item number.

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

On that same page, Jon shows ways in which you can take code that was recorded by the macro recorder and make it more streamlined and efficient.
Using just a few techniques you will be able to cut the number of line of code you have by 50% or more.


Wonderful, thanks Jerry! My only problem is how to delete the legend entry for my newly created series. Not sure how to delete a specific legend entry (in this case for the series named "Event 1"). Before, I just selected the 6th series and that worked. But not it's not necessarily the 6th. Any thoughts?


For others with a similar question:

Code:
Sub AddNewEventSeries()
'adds a new series to the active chart
With ActiveChart.SeriesCollection.NewSeries
.Name = "Event 1"
.ChartType = xlXYScatterLines
.AxisGroup = 2
.XValues = "='Intructions'!$Z$3:$Z$4"
.Values = "='Intructions'!$AA$3:$AA$4"
.Select
.Format.Line.Weight = 1.5
.Format.Line.Visible = msoCTrue
.Format.Line.ForeColor.RGB = vbBlack
.Format.Line.DashStyle = msoLineSysDash
.MarkerStyle = -4142
End With


With ActiveChart.SeriesCollection.NewSeries
.Name = "Event 2"
.ChartType = xlXYScatterLines
.AxisGroup = 2
.XValues = "='Intructions'!$Z$5:$Z$6"
.Values = "='Intructions'!$AA$5:$AA$6"
.Select
.Format.Line.Weight = 1.5
.Format.Line.Visible = msoCTrue
.Format.Line.ForeColor.RGB = vbBlack
.Format.Line.DashStyle = msoLineSysDash
.MarkerStyle = -4142
End With


With ActiveChart.Axes(xlValue, xlSecondary)
.MajorTickMark = xlNone
.TickLabelPosition = xlNone
.MaximumScale = 1
.MinimumScale = 0
'NOTE Legend code doesn't work
'.Legend.LegendEntries("Event 1").Select
' Selection.Delete
End With

End Sub
 

MacGyver7640

Board Regular
Joined
Oct 28, 2011
Messages
76
Got it!

Code:
With ActiveChart.Legend
.LegendEntries(ActiveChart.Legend.LegendEntries.Count).Delete
.LegendEntries(ActiveChart.Legend.LegendEntries.Count).Delete

Written twice to delete the legend entries for the last two series I added.
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
I'm glad you were able to find a solution. I expected be able to find a more direct way to delete a specific legend entry associated with a specific data series. IE: Delete the Legend entry associated with series "2011 Sales". No such luck!

Reading a bit more about Legends on Jon Peltier's site, I'm not seeing an obvious way to do this. The best that I could think of would be to delete the legend then add a new legend to realign the order with the series collection.

So your solution looks like the best for now!

Cheers!
 

MacGyver7640

Board Regular
Joined
Oct 28, 2011
Messages
76
I'm glad you were able to find a solution. I expected be able to find a more direct way to delete a specific legend entry associated with a specific data series. IE: Delete the Legend entry associated with series "2011 Sales". No such luck!

Reading a bit more about Legends on Jon Peltier's site, I'm not seeing an obvious way to do this. The best that I could think of would be to delete the legend then add a new legend to realign the order with the series collection.

So your solution looks like the best for now!

Cheers!

Yea, looks like legends are tricky. Thanks for your help! One more VBA problem checked off the list.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,474
Messages
5,601,880
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top