I'm currently facing an issue with a VBA macro in Excel. In my workbook, I have a macro designed to trigger whenever there are changes in Slicer data. The goal is to use the Workbook_SlicerChange function to update a specific cell based on the presence of active filters. However, it seems that the macro isn't being triggered as expected when I interact with Slicer data.
It always prints 0 when there is any change in the data slicer. Before, it never printed anything at any time. So we made progress.
However, what I need is for it to print 0 when no filter is selected and 1 when any filter is selected.
I'm a novice in VBA. In the debugging I did with the help of Chat GPT, it indicated that the code was unable to detect the slicers when determining whether there was a filter or not, as if they didn't exist.
I appreciate the effort of anyone who can help in advance. Thank you.
Here's a brief overview of my workbook structure: I have a Slicer in the 'DRE TOTAL' sheet, while the pivot table is in the 'Tabela Fonte' sheet, and the macro is intended to respond to changes in this Slicer.
Here's a snippet of the VBA code I'm using:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
' Atualiza a célula B3 na planilha 'DRE TOTAL' com 1 se houver filtros ativos, 0 se não houver
ThisWorkbook.Sheets("DRE TOTAL").Range("B3").Value = IIf(ExistemFiltrosAtivos(), 1, 0)
End Sub
Function ExistemFiltrosAtivos() As Boolean
' Verifica se há filtros ativos em qualquer das Segmentações de Dados especificadas
Dim slicer As slicer
Dim slicerNames As Variant
Dim name As Variant
' Define os nomes das Segmentações de Dados
slicerNames = Array("Descr Un Analítica", "Un resumo", "Un gestor", "Un Operação")
' Itera por cada nome de Segmentação de Dados
For Each name In slicerNames
On Error Resume Next ' Ignora erros
Set slicer = ActiveWorkbook.Slicers(name)
On Error GoTo 0 ' Para de ignorar erros
' Verifica se a Segmentação de Dados existe
If Not slicer Is Nothing Then
' Verifica se há filtros ativos na Segmentação de Dados
If slicer.SlicerCache.VisibleSlicerItems.Count < slicer.SlicerCache.SlicerItems.Count Then
ExistemFiltrosAtivos = True
Exit Function ' Sai do loop se encontrar qualquer filtro ativo
End If
End If
Next name
' Se não houver filtros ativos em nenhuma Segmentação de Dados, retorna Falso
ExistemFiltrosAtivos = False
End Function
It always prints 0 when there is any change in the data slicer. Before, it never printed anything at any time. So we made progress.
However, what I need is for it to print 0 when no filter is selected and 1 when any filter is selected.
I'm a novice in VBA. In the debugging I did with the help of Chat GPT, it indicated that the code was unable to detect the slicers when determining whether there was a filter or not, as if they didn't exist.
I appreciate the effort of anyone who can help in advance. Thank you.
Here's a brief overview of my workbook structure: I have a Slicer in the 'DRE TOTAL' sheet, while the pivot table is in the 'Tabela Fonte' sheet, and the macro is intended to respond to changes in this Slicer.
Here's a snippet of the VBA code I'm using:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
' Atualiza a célula B3 na planilha 'DRE TOTAL' com 1 se houver filtros ativos, 0 se não houver
ThisWorkbook.Sheets("DRE TOTAL").Range("B3").Value = IIf(ExistemFiltrosAtivos(), 1, 0)
End Sub
Function ExistemFiltrosAtivos() As Boolean
' Verifica se há filtros ativos em qualquer das Segmentações de Dados especificadas
Dim slicer As slicer
Dim slicerNames As Variant
Dim name As Variant
' Define os nomes das Segmentações de Dados
slicerNames = Array("Descr Un Analítica", "Un resumo", "Un gestor", "Un Operação")
' Itera por cada nome de Segmentação de Dados
For Each name In slicerNames
On Error Resume Next ' Ignora erros
Set slicer = ActiveWorkbook.Slicers(name)
On Error GoTo 0 ' Para de ignorar erros
' Verifica se a Segmentação de Dados existe
If Not slicer Is Nothing Then
' Verifica se há filtros ativos na Segmentação de Dados
If slicer.SlicerCache.VisibleSlicerItems.Count < slicer.SlicerCache.SlicerItems.Count Then
ExistemFiltrosAtivos = True
Exit Function ' Sai do loop se encontrar qualquer filtro ativo
End If
End If
Next name
' Se não houver filtros ativos em nenhuma Segmentação de Dados, retorna Falso
ExistemFiltrosAtivos = False
End Function
