VBA Macro Not Responding to Slicer Changes in Excel Workbook

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.

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:

vba
Copy code
Private Sub Workbook_SlicerChange(ByVal SlicerCache As SlicerCache)
' Update cell B3 in the 'DRE TOTAL' sheet with 1 if there are active filters, 0 if not
ThisWorkbook.Sheets("DRE TOTAL").Range("B3").Value = IIf(ExistemFiltrosAtivos(), 1, 0)
End Sub

Function ExistemFiltrosAtivos() As Boolean
' Check if there are active filters in all Slicers in the 'DRE TOTAL' sheet
Dim slicer As SlicerCache
Dim wsDRE As Worksheet

' Define the worksheet where Slicers are located
Set wsDRE = ThisWorkbook.Sheets("DRE TOTAL")

' Iterate through all Slicers in the 'DRE TOTAL' sheet
For Each slicer In wsDRE.SlicerCaches
If slicer.VisibleSlicerItems.Count < slicer.SlicerItems.Count Then
ExistemFiltrosAtivos = True
Exit Function ' Exit the loop if any active filter is found
End If
Next slicer

' If there are no active filters in any Slicer, return False
ExistemFiltrosAtivos = False
End Function
I would greatly appreciate any insights or suggestions on why the macro isn't responding to Slicer changes as expected. Thank you!

I have already advanced the code, but it is still not working correctly.

Now it always prints 0 when there is a change in data segmentation. Before, I never printed anything at any time. We made a breakthrough. However, what I need is that when no filter is selected it prints 0, when any filter is selected it prints 1.

The code looks like this:

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 ****í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