Getting rid of plot markers and labels 1

[electricpete]

I have a spreadsheet which displays an interactive plot for browsing and manipulating large amount of numeric data.

One feature I have is that pushing a buttom with the cursor on a given point in the graph labels that point. It's a little complicated how the label is applied, and not really needed to answer the question, but the code that sets up the labels and markers is shown below for info (and it works fine):

Private Sub CommandButton4_Click()        ' label current t
Dim series As Integer
Dim point As Integer
series = Sheets("plotTWFs").Range("currentseries").VALUE        ' point to currentseries index (integer)
point = Sheets("plotTWFs").Range("currentpoint").VALUE        ' point to currentpoint index (integer)
    Sheets("plotTWFs").ChartObjects(1).Activate
    ActiveChart.SeriesCollection(series).Points(point).Select
    With Selection
        .MarkerBackgroundColorIndex = 3
        .MarkerForegroundColorIndex = 3
        .MarkerStyle = xlCircle
        .MarkerSize = 3
        .Shadow = False
    End With
    ActiveChart.SeriesCollection(series).Points(point).ApplyDataLabels Type:= _
                                                                       xlDataLabelsShowLabel, AutoText:=True, LegendKey:=False
    ActiveChart.SeriesCollection(series).Points(point).DataLabel.Select
    Selection.AutoScaleFont = False
    With Selection.Characters(Start:=1, Length:=11).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
    ActiveChart.SeriesCollection(series).DataLabels.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = xlUpward
        .Position = xlLabelPositionAbove
        .Orientation = 90
    End With

End Sub

Now the problem comes when I want to get rid of those markers and labels. I have a button that is supposed to turn off all the markers and labels. The code is below. When I push the bottom it executes for hours (I have never let it run to completion). I think the problem is that it is going through every point in the large set of data when deleting labels. Is there a way to delete all labels for a series without going through every point in the series?

Private Sub EraseTWFlabels_Click()
Dim ThisSeries As series
Dim ThisChart As Chart
Dim ThisDataLabel As DataLabel
    Set ThisChart = Sheets("plotTWFs").ChartObjects(1).Chart
    For Each ThisSeries In ThisChart.SeriesCollection
        ThisSeries.MarkerStyle = xlMarkerStyleNone
               
        For Each ThisDataLabel In ThisSeries.DataLabels
                ThisDataLabel.Delete
        Next ThisDataLabel
    Next ThisSeries
    
End Sub

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[joerd]

Instead of

For Each ThisDataLabel In ThisSeries.DataLabels
      ThisDataLabel.Delete
Next ThisDataLabel

try

ThisSeries.HasDataLabels = False

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[electricpete]

That works soooo much better.
Thanks.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.