X Y Scatter Graph Filtered with Data labels

[The_Rock]

Hi All
I found the following code on the MS Support site which adds custom data labels to scatter graphs.
It working, however when I filter the data and then run the macro I get an 'Invalid Parameter' error.
Any idea on how to modify it so it says to count only the visible cells?

Thanks

Sub AttachLabelsToPoints()

   'Dimension variables.
   Dim Counter As Integer, ChartName As String, xVals As String

   ' Disable screen updating while the subroutine is run.
   Application.ScreenUpdating = False

   'Store the formula for the first series in "xVals".
   xVals = ActiveChart.SeriesCollection(1).Formula

   'Extract the range for the data from xVals.
   xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
      Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
   xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
   Do While Left(xVals, 1) = ","
      xVals = Mid(xVals, 2)
   Loop

   'Attach a label to each data point in the chart.
   For Counter = 1 To Range(xVals).Cells.Count
     ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
         True
      ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
         Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
   Next Counter

End Sub

 

[RickXL]

Hi.

Could you run things the other way round?

That is, run the macro without the filter first then apply the filter afterwards?

 

[The_Rock]

Yep, that can also work, was hoping for a lazy workaround

Thanks

 

[RickXL]

So was I. That is why I suggested it!

 

[Jon Peltier]

In 2013, filtering the points keeps the labels attached to the same points. In 2010 and earlier, however, the pre-filter labels are used on the filtered data, so if you filter out the even numbered points (for example), points 1, 3, 5, 7, and 9 will assume the labels 1, 2, 3, 4, 5.

This quick rewrite of your procedure will place the appropriate labels on the corresponding points in the unfiltered or filtered chart:

Sub AttachLabelsToPoints()

   'Dimension variables.
   Dim xVals As String
   Dim iPt As Long, iRow As Long

   ' Disable screen updating while the subroutine is run.
   Application.ScreenUpdating = False

   'Store the formula for the first series in "xVals".
   xVals = ActiveChart.SeriesCollection(1).Formula

   'Extract the range for the data from xVals.
   xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
      Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
   xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
   Do While Left(xVals, 1) = ","
      xVals = Mid(xVals, 2)
   Loop

   'Attach a label to each data point in the chart.
   For iPt = 1 To ActiveChart.SeriesCollection(1).Points.Count
      ActiveChart.SeriesCollection(1).Points(iPt).HasDataLabel = _
         True
      iRow = iRow + 1
      Do
        If Not Range(xVals).Cells(iRow, 1).EntireRow.Hidden Then Exit Do
        ' move up a row if current row is filtered (hidden)
        iRow = iRow + 1
      Loop
      ActiveChart.SeriesCollection(1).Points(iPt).DataLabel.Text = _
         Range(xVals).Cells(iRow, 1).Offset(0, -1).Value
   Next iPt

End Sub

 

[The_Rock]

Jon - apologies for the late reply, I got called away to work on another project and didn't get a chance to look at this.
You are a legend - this works perfectly

 

[RickXL]

Jon ... You are a legend

+1

 

[wilkisa]

Jon Peltier: I know this is 3 years after but my user was having the same issue. I search MrXL and found this code. I entered it into his heat map chart and it works perfectly. Thank you so much!!!