PDA

View Full Version : [SOLVED] Problem with Deleting Select Legend Keys



nreynolds
01-02-2015, 03:36 PM
I trying to develop a macro that will populate a number of data points onto an Excel XY Scatter plot that already has two data series plotted. The macro currently works fine as far as loading the data points as a series and a few edits to position corresponding data labels. However, I would also like the legend to only show information on the two original data series. The subsequent data points should only be labeled on the chart using data labels....not the chart legend. Below is the code I'm trying to use for deleting the legend entries.

Private Sub ClearLegend()
Dim z As Integer
Worksheets("HGL vs Pipeline").ChartObjects("Pipeline Profile").Activate
For z = ActiveChart.SeriesCollection.Count To 3 Step -1
ActiveChart.Legend.LegendEntries(z).Delete
Next
End Sub


It may also be important to note that on the same worksheet I have a macro-enabled button that clears the chart. The first time I populate the chart, the legend keys are deleted as anticipated. However, if I clear the chart and attempt the re-populate with the data points, I get the following error: "System Error &H80004005 (-2147467529). Unspecified error"

Perhaps, the issue lies somewhere in the chart clearing process? That coding is shown below.


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 idea is to allow the user to populate the chart with the certain data points. Then, if changes need to be made later, they can clear the chart and then display whatever new point information they wish...let me know if more information is needed.

westconn1
01-05-2015, 01:35 PM
can you post a workbook with sample data?

nreynolds
01-14-2015, 08:47 AM
12715

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.

Aflatoon
01-15-2015, 01:46 AM
Try this:

Dim z As Integer
With ActiveSheet.ChartObjects("Pipeline Profile").Chart.Legend.LegendEntries
For z = .Count To 3 Step -1
.Item(z).Delete
Next
End With

nreynolds
01-15-2015, 06:54 AM
Works perfect. Thanks for helping out. Just for future knowledge, what was the issue with my original code?

Aflatoon
01-15-2015, 07:02 AM
It was trying to delete legendentries that weren't there. It's always better to use the count of the collection you are deleting from as your control variable.

Bart L
04-14-2020, 12:55 PM
The script removed all the time different legend entries, very randomly, also with the code from Aftaloon, but after endless attempts I found a solution.
I removed the legend and placed it back :)



ochart.Legend.Delete
ochart.SetElement (msoElementLegendRight)


With ochart.Legend.LegendEntries
For z = .Count - NumberOfSeries To NumberOfSeries + 1 Step -1
.Item(z).Delete
Next
End With


Now only the right legend entries are removed when I press my refresh button. I think some entries were not yet made before the removing started? Is that even possible?

I hope this helps some other people struggling with this in the future.