Этот макрос отображает текстовые метки из исходного листа, если дважды щелкнуть точку на диаграмме рассеяния. Диаграмма обновляется, когда исходный лист фильтруется по любому из нескольких столбцов. Макрос должен обнаруживать эту фильтрацию и обновлять значения меток соответственно.
Это работает, когда данные сортируются по отфильтрованному столбцу, но не когда они также сортируются по другому столбцу, по которому данные не сортируются, что вызывает дополнительные скрытые строки.
Проблема в том, что, несмотря на то, что некоторые строки скрыты, макрос считает как видимые, так и скрытые строки, начиная с первой видимой строки (как если бы xlCellTypeVisible
не работал).
Для пояснения: до тех пор, пока в отфильтрованном подмножестве нет скрытых строк, метки отображаются правильно, начиная с первой видимой строки в отфильтрованном подмножестве. Однако, когда к столбцу применяется дополнительный фильтр, который не отсортирован по отфильтрованному значению, маркировка искажается из-за подсчета скрытых вкрапленных строк в дополнение к видимым строкам.
Детали: - Начальная строка рассчитывается правильно, какой бы ни была первая видимая строка. - Значение Arg2 также правильно установлено для соответствующей видимой строки в серии, а значения xData и yData на метке являются правильными даже при фильтрации по несортированным строкам! (Таким образом, Arg2 пропускает любые скрытые строки в соответствии с сериями, отображаемыми на графике.)
Но метки из других столбцов неверны.
По сути, мне нужно извлечь текст метки из номера строки Arg2 из диапазона видимых строк на отфильтрованном листе.
Я предполагаю, что проблема в разделе sid = .cells
где счетчик использует все строки, а не только видимые строки. Опять же, действительные значения (при отображении с помощью Msgbox) указывают на правильную видимую строку, если я визуально считаю строки на исходном листе. Но фактический текст в метке основан на применении этого счетчика как к скрытым, так и к видимым строкам, поэтому получается неправильная строка, которая находится выше в данных.
Я попытался изменить sid = .cells
на sid = r.cells
но не повезло, фактически он начинает считать с самой первой строки в данных, а не с первой видимой строки. Похоже, что SpecialCells(xlCellTypeVisible)
работает только так, как и ожидалось, при идентификации первой видимой строки, но запутывается в любых последующих скрытых строках.
Любая помощь будет оценена. Я новичок в VBA, поэтому, пожалуйста, будьте ясны / конкретны!
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