Esta macro exibe rótulos de texto da planilha de origem ao clicar duas vezes em um ponto em um gráfico de gráfico de dispersão. O gráfico é atualizado quando a planilha de origem é filtrada em qualquer uma das várias colunas. A macro deve detectar essa filtragem e atualizar os valores do rótulo de acordo.
Isso funciona quando os dados são classificados na coluna filtrada, mas não quando também são classificados em outra coluna na qual os dados não são classificados, o que causa linhas ocultas adicionais.
O problema é que, apesar do fato de algumas linhas estarem ocultas, a macro conta tanto linhas visíveis como ocultas, começando da primeira linha visível (como se xlCellTypeVisible não estivesse funcionando).
Para esclarecer: enquanto não houver linhas ocultas no subconjunto filtrado, os rótulos serão exibidos corretamente, começando pela primeira linha visível no subconjunto filtrado. No entanto, quando um filtro adicional é aplicado em uma coluna que não é classificada pelo valor filtrado, a etiquetagem é distorcida devido à contagem das linhas ocultas intercaladas, além das linhas visíveis.
Detalhes:
- A linha inicial é calculada corretamente para qualquer que seja a primeira linha visível. - O valor Arg2 também é definido corretamente para a linha visível apropriada na série, e os valores xData e yData no rótulo estão corretos mesmo quando filtrados em linhas não ordenadas! (Assim, o Arg2 pula todas as linhas ocultas, consistente com a série exibida no gráfico).
Mas os rótulos de outras colunas estão incorretos.
Basicamente, eu preciso buscar meu texto de marcador do número de linha Arg2 do intervalo de linhas visíveis na planilha filtrada.
Acredito que o problema esteja na seção sid = .cells , em que a contagem usa todas as linhas, em vez de apenas linhas visíveis. Novamente, as contagens reais (quando exibidas com Msgbox) apontam para a linha visível correta se eu visualmente contar as linhas na planilha de origem. Mas o texto real no rótulo é baseado na aplicação dessa contagem a linhas ocultas e visíveis, portanto, surge com uma linha incorreta que é mais alta nos dados.
Eu tentei alterar sid = .cells para sid = r.cells , mas sem sorte, na verdade, ele começa a contar a partir da primeira linha nos dados, em vez da primeira linha visível. Parece que o SpecialCells(xlCellTypeVisible) está funcionando apenas como esperado ao identificar a linha visível primeiro , mas fica confuso com as linhas ocultas subseqüentes.
Qualquer ajuda seria apreciada. Eu sou novo no VBA, então seja claro / específico!
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