Test.xlsx
Worksheet is attached. The current coding for this sheet is as follows:
Sub Profile()
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Pipeline Profile").Activate
ActiveSheet.Activate
Dim i As Integer, a As Integer, b As Integer
a = 112
b = 3
For i = 67 To a
If IsEmpty(ActiveSheet.Cells(i, 5).Value) Then
Exit For
Else
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(b).Name = ActiveSheet.Cells(i, 5).Value
ActiveChart.SeriesCollection(b).XValues = ActiveSheet.Cells(i, 3).Value
ActiveChart.SeriesCollection(b).Values = ActiveSheet.Cells(i, 4).Value
ActiveChart.SeriesCollection(b).Select
Selection.MarkerSize = 15
Selection.Format.Line.Visible = msoFalse
ActiveChart.SeriesCollection(b).ApplyDataLabels
ActiveChart.SeriesCollection(b).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowValue = False
Selection.Position = xlLabelPositionBelow
ActiveChart.SeriesCollection(b).Points(1).DataLabel.Select
Selection.Format.TextFrame2.TextRange.Font.Size = 15
End If
b = b + 1
Next
ActiveSheet.ChartObjects("Pipeline Profile").Activate
Dim z As Integer
For z = ActiveChart.SeriesCollection.Count To 3 Step -1
ActiveChart.Legend.LegendEntries(z).Delete
Next
End Sub
Sub ClearChart()
Dim i As Integer
ActiveSheet.ChartObjects("Pipeline Profile").Activate
ActiveChart.PlotArea.Select
For i = ActiveChart.SeriesCollection.Count To 3 Step -1
ActiveChart.SeriesCollection(i).Delete
Next
End Sub
The user will essentially enter station, elevation, and description data in the table below the drawing sheet. The Profile button/macro should then load these as points onto the the chart above (the pipeline and ground data will probably not be loaded correctly since they would be entered on a differnet sheet). After populating these points, I'd like to legend to only show the two original lines (labled "Pipeline Invert" and "Ground Elevation"). The error pops up if I run the code more than once (clear chart and try to repopulate with editted data). As far as I can tell, the error has something to do with trying to delete legend entries that may have previously existed...not real sure though. Thanks for taking a look.