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
xlCellTypeVisibleis 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