Jon Peltier's LastPointLabel

[Phil Hageman]

In Module3 of a workbook (solely dedicated to
LastPointLabel) I have downloaded/copied the following
code from your geocities website:
Option Explicit
Sub Create_Menu().
Sub Delete_Menu().
Sub LastPointLabel().

Opening the worksheet containing imbedded charts, I run
the LastPointLabel macro and receive: Run-time error '91':
Object variable or With block variable not set.

The line: For Each. in the code is highlighted yellow

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
nPts = .Points.Count
mySrs.Points(nPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
mySrs.Points(nPts).DataLabel.Text = mySrs.Name
End With
Next
End Sub

What do I need to do to make this work?

 

[Jon Peltier]

Phil -

You need to select a chart. A "more advanced" version of this procedure
is listed below. It checks for an active chart, and it finds the last
point that will accept a label (i.e., the value isn't a blank or #N/A).

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long, iPt As Long
Dim ErrNum As Long
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, "No Chart
Selected"
Else
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
nPts = .Points.count
For iPt = nPts To 1 Step -1
On Error Resume Next
mySrs.Points(iPt).HasDataLabel = True
mySrs.Points(iPt).DataLabel.Text = mySrs.name
ErrNum = Err.Number
On Error GoTo 0
If ErrNum = 0 Then Exit For
Next
End With
Next
End If
End Sub

- Jon

 

[Phil Hageman]

IT WORKS GREAT!! Thank you very much. Some questions:
1. Can we make the code work for all charts on a
worksheet - automatically?
2. Is there a way to specify specific serieses? My
charts are combination area charts, with lines ploted. I
want to label two line series that are putting a straight
line across the chart (constant values). These I want to
label, no others.
3. Is there a way to make the labels permanent where the
user doesn't have to invoke the macro?

4. Also, when I run the macro on a selected chart, the
chart flickers for a few seconds and then settles. I'm
sure users will object to this. Is there a way to avoid
seeing this?

Thanks again Jon for a fantastic feature. Appreciate it.

Phil

 

[Jon Peltier]

Wow, Phil. You're tough. Anyway, all of your requests are possible.
Number 3 is toughest, because you'd need to set up worksheet events to
change the labels (and the point being labeled, the number of series
being labeled, etc.) when required. You may want to use a cell link
instead of a static label (like "=Sheet1!R1C1" instead of mySrs.Name in
this code).

Numbers 1, 2, and 4 are easily integrated:

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long, iPt As Long
Dim ErrNum As Long
Dim ChtOb As ChartObject

'' 4. Stop Screen Flicker (Not sure about effectiveness)
Application.ScreenUpdating = False

'' 1. All Charts on a Worksheet
For Each ChtOb In ActiveSheet.ChartObjects
For Each mySrs In ChtOb.Chart.SeriesCollection

'' 2. Line Series Only
If mySrs.ChartType = xlLine Or _
mySrs.ChartType = xlLineMarkers Then
With mySrs
nPts = .Points.Count
For iPt = nPts To 1 Step -1
On Error Resume Next
mySrs.Points(iPt).HasDataLabel = True
mySrs.Points(iPt).DataLabel.Text = mySrs.Name
ErrNum = Err.Number
On Error GoTo 0
If ErrNum = 0 Then Exit For
Next
End With
End If
Next
Next
Application.ScreenUpdating = True
End Sub

- Jon