Label Last Point – Updated Add-In

On my web site I wrote a tutorial that showed how to Label the Last Point in an Excel Chart’s Series, and I posted a small add-in with the labeling procedure. I later blogged about this technique in Label Each Series in a Chart. When Excel 2007 came around, I discovered that the code no longer worked as expected because Excel 2007 treats unplottable points (blank cells or #N/A errors) differently than Excel 2003 had. I figured out a workaround and blogged about it in Label Last Point for Excel 2007. I never posted the corresponding add-in for Excel 2007.

The problem with the code in these pages is that they only labeled the active chart. The problem with this is that I often have a dozen or more charts to label, and it’s tedious to select and run the code, select and run the code, select and run the code, etc. So I recently made a minor adjustment to the code that would apply last point labels to the active chart, or to all selected charts. (You can select multiple charts by holding Shift or Ctrl while clicking on them.)

It’s almost embarrassing how long it took me to enhance the code. The reason I didn’t is another “feature” of Excel 2007. In previous versions of Excel, when I wanted to carry out an action on the selected charts on a worksheet, I could check out the selected shapes, determine which shapes were chart objects, and act on each chart object.

      Dim myChart As ChartObject
      Dim myShape As Shape

      ''' SELECTED SHAPES ON THE SHEET
      For Each myShape In Selection.ShapeRange
        ''' IS SHAPE A CHART?
        On Error Resume Next
        Set myChart = ActiveSheet.ChartObjects(myShape.Name)
        If Not Err Then
          ' check for surface chart (disallowed)
          If myChart.Chart.ChartType < 83 Or myChart.Chart.ChartType > 86 Then
            ''''''''''
            '' do what needs to be done
            ''''''''''
          End If
        End If
        On Error GoTo 0
      Next

For some reason, Excel 2007 was unable to detect which chart objects were selected, so it would process every chart object on the sheet. Not cool if you have painstakingly added a dummy series and applied custom data labels to the chart you had not selected.

I learned that you could add a shape to the sheet, and add this shape to the selection, and Excel 2007 would recognize which chart objects were actually selected. But this is a bit more contrived than I like. Not as elegant as my usual code (ha ha).

Leave it to my friend and colleague Andy Pope to find a workaround. Andy is great at looking at things a bit sideways and seeing what the rest of us have overlooked. Visit Andy’s site for a bunch of imaginative charting examples.

Anyway, Andy realized that the selection would consist of a set of objects, and you can test whether each object in the selection is a chart object. This approach works just fine in 2007 (and in 2003!).

      Dim obj As Object

      '' check for error (i.e., selection isn't a bunch of shapes)
      On Error Resume Next
      For Each obj In Selection
        If TypeName(obj) = "ChartObject" Then
          ' check for surface chart (disallowed)
          If obj.Chart.ChartType < 83 Or obj.Chart.ChartType > 86 Then
            ''''''''''
            '' do what needs to be done
            ''''''''''
          End If
        End If
      Next
      On Error GoTo 0

So finally I expanded my routine. I’ve packed it into an add-in, which works just fine in Excel 2003 and 2007. Download the zip file PTSLabelLastPoint.zip and unpack the add-in (PTSLabelLastPoint.xla). Install it using the applicable protocol in Installing an Excel Add-In in Excel 2003 or in Installing an Add-In in Excel 2007.

 

Peltier Tech Charts for Excel

Comments

  1. Jon,

    I noticed your add-in here doesn’t have the exact same functionality as your original tutorial code for Excel 2003.

    With your new PTS Charts add-in, existing series labels are no longer reset to the SeriesName, which can be handy when one wants to substitute Label Last Point for a legend. This different behavior only arises if one has labeled a series or chart or last point with something other than the series name along the way. For instance, if someone wants to fix their space-consuming legends and cluttered data value labels, they have to turn off the point labels manually before the new Label Last Point can work its magic.

  2. Hi Dale –

    Seems to be a version control issue one my end. The problem is that I have a number of versions of this utility, including one in my personal macro add-in and more than one standalone. I need to get the latest and greatest before I add this last functionality.

  3. Hi, Jon,

    Thanks for posting this new code. Viewing the code is always a great learning experience for me. If you have some time, I’d really love to see a post about how to make add-ins. I often share macros with other folks in my organization by pasting code in an email. It would be really nice to compile some of the better macros and distribute to the less technical colleagues as an add-in. Thanks!

  4. Dale –

    The problem was easy to fix. When the point already has a label with a formula (maybe any label, I didn’t check), the AddDataLabel method doesn’t change the label.

    The solution? Delete the label before adding the label that is called for in the program.

    I’ve updated a corrected version of the utility.

  5. Hello Jon,

    Thanks a lot for posting your work. It has been very useful to me!

    I am writing because I have two questions:
    1. Could you please post the macro for Excel 2007 that includes the adjustment to the code that would apply last point labels to the active chart or to all selected charts?
    2. I know how to install an Add-in… but I do not know how to “unpack the add-in (PTSLabelLastPoint.xla)”. Could you please explain the process of downloading and unpacking the add-in?

    Best regards,

    Ignacio

  6. Ignacio –

    I meant unzip the downloaded zip file to extract the add-in file (PTSLabelLastPoint.xla), then use the protocol at the link provided to install the file as an Excel add-in.

    The code you’ve requested is in the add-in.

Trackbacks

  1. […] an entry for shop_average. But if you label the series and delete the legend, this will not matter. Apply a label to the last point of each series using the Series […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0