VBA Modifying charts: Adding a new series

M

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 :)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
JS411 said:
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.
Click to expand...


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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
JS411 said:
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!
Click to expand...

Yea, looks like legends are tricky. Thanks for your help! One more VBA problem checked off the list.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
VBA code for fill color and transparency in a 3 series line chart with only one series as an area series
Replies
0
Views
659
travellerva
T
P
  • Question
Turning OFF the visibility of a horizontal (category) axis labels if certain conditions apply
Replies
1
Views
390
NdNoviceHlp
NdNoviceHlp
A
  • Question
VBA problem with setting data source range
Replies
1
Views
848
Akbarov
A
KillGorack
  • Question
Bind chart data without selecting/activating
Replies
3
Views
352
Domenic
D
KillGorack
  • Question
Binding Chart, not matching cell values (VBA)
Replies
1
Views
385
KillGorack
KillGorack

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

Share this page

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