If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Jon Peltier's LastPointLabel
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? |
#2
|
|||
|
|||
Jon Peltier's LastPointLabel
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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Phil Hageman wrote: 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? |
#3
|
|||
|
|||
Possible Fine Tuning
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 -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Phil Hageman wrote: 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? . |
#4
|
|||
|
|||
Possible Fine Tuning
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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Phil Hageman wrote: 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 -----Original Message----- 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 ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Phil Hageman wrote: 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? . |
Thread Tools | |
Display Modes | |
|
|