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
Thank you guys so much! I apologize if my code is messy, just learning VBA
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