This macro displays text labels from the source worksheet when double-clicking a dot on a scatterplot chart. The chart is updated when the source worksheet is filtered on any of several columns. The macro is supposed to detect this filtering, and update the label values accordingly.
This works when the data are sorted on the filtered column, but not when it is also sorted on some other column on which the data are not sorted, which causes additional hidden rows.
The problem is that despite the fact that some rows are hidden, the macro counts both visible AND hidden rows, starting from the first visible row (as if xlCellTypeVisible
is not working).
To clarify: As long as there are no hidden rows in the filtered subset, the labels display correctly starting with the first visible row on the filtered subset. However when an additional filter is applied on a column that is not sorted by the filtered value, the labeling gets screwed up due to counting of the interspersed hidden rows in addition to the visible rows.
Details: - The starting row is calculated correctly to whatever the first visible row is. - The Arg2 value is also correctly set to the appropriate visible row in the series, and the xData and yData values on the label are correct even when filtered on non-sorted rows! (So Arg2 does skip any hidden rows, consistent with the series that is displayed on the chart.)
But the labels from other columns that are incorrect.
Basically, I need to fetch my label text from row number Arg2 from the range of visible rows on the filtered sheet.
I am guessing the problem is in sid = .cells
section where the count uses all rows instead of just visible rows. Again, the actual counts (when displayed with Msgbox) do point to the correct visible row if I visually count down the rows on the source worksheet. But the actual text in the label is based on applying this count to both hidden and visible rows, thus it comes up with a wrong row that is higher up in the data.
I have tried changing sid = .cells
to sid = r.cells
but no luck, in fact it starts counting from the very first row in the data rather than the first visible row. It looks like the SpecialCells(xlCellTypeVisible)
is only working as expected when identifying the first visible row, but gets confused on any subsequent hidden rows.
Any help would be appreciated. I am new to VBA so please be clear/specific!
Public WithEvents myChartClass As Chart
Private Sub myChartClass_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Dim ser As Series
Dim pt As Point
Dim xData As Double, yData As Double
Dim sid As String
'declare vars used for calculating row number for filtered data
Dim r As Range
Dim StartRow As Long
Cancel = True
For Each ser In Me.SeriesCollection
ser.HasDataLabels = False
Next
If ElementID = xlSeries Then
If Arg2 > 0 Then
With Worksheets("MySheetName")
Set ser = Me.SeriesCollection(Arg1)
xData = ser.XValues(Arg2)
yData = ser.Values(Arg2)
Set pt = ser.Points(Arg2)
'calculate starting row when table is filtered on any variable
Set r = Worksheets("MySheetName").Range("A:A").Rows.SpecialCells(xlCellTypeVisible)
StartRow = r.Row - 1 'starting row is the first visible row minus the table header
'grab label from the row associated with the clicked point on chart
'the case number signifies the series of the chart in the order visible in Select Data chart properties window
Select Case Arg1
Case 1 'series 1
sid = .Cells(Arg2 + StartRow, "D") & vbLf & "label1: " & .Cells(Arg2 + StartRow, "C") & vbLf & "label2: " & .Cells(Arg2 + StartRow, "L") & vbLf & "label3: " & .Cells(Arg2 + StartRow, "U")
Case 2 'series 2
sid = .Cells(Arg2 + StartRow, "D") & vbLf & "label1: " & .Cells(Arg2 + StartRow, "C") & vbLf & "label2: " & .Cells(Arg2 + StartRow, "L") & vbLf & "label3: " & .Cells(Arg2 + StartRow, "U")
End Select
pt.HasDataLabel = True
pt.DataLabel.Characters.Font.Size = 11
pt.DataLabel.Characters.Font.Bold = True
pt.DataLabel.Text = sid & vbLf & "(" & xData & " , " & yData & ")"
'MsgBox "r: " & r.Count
'MsgBox "StartRow: " & StartRow
'MsgBox "Arg1: " & Arg1
'MsgBox "Arg2: " & Arg2
End With
End If
End If
End Sub