Activate Macro based on slicer selection

[Corleone]

Hi - I would like to set this slicer up so that activates a macro depending on which box i select below - eg if select the planners box it activates a macro called "Planning_Staff"

Ive been offered this as a solution but nothing happens when i click the Planners box
for info i can confirm that the name of the slicer is "ROLE" and there is a macro in the workbook entitled "Planning_Staff"


Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim ws As Worksheet
Dim slicer As slicer
Dim selectedSlicerItem As SlicerItem

'Specify the name of the worksheet containing the slicer
Set ws = ThisWorkbook.Sheets("GRAPHS") 'Replace "Sheet1" with the actual name of your worksheet

'Check if the sheet contains a slicer with the specified name
On Error Resume Next
Set slicer = ws.Slicers("ROLE")
On Error GoTo 0

'Exit if the specified slicer doesn't exist on the sheet
If slicer Is Nothing Then Exit Sub

'Check if the slicer has any selected items
If slicer.SlicerCache.VisibleSlicerItems.Count > 0 Then
'Loop through selected items
For Each selectedSlicerItem In slicer.SlicerCache.VisibleSlicerItems
'Check if the selected item is "Planners"
If selectedSlicerItem.Name = "Senior PM" Then
'Call the macro for "Planners"
Call Planning_Staff
Exit For 'Exit the loop after finding "Planners"
End If
Next selectedSlicerItem
End If
End Sub

 

[John_w]

Slicers is a member of the PivotTable class, not the Worksheet sheet. Therefore replace:

On Error Resume Next
Set slicer = ws.Slicers("ROLE")
On Error GoTo 0

with:

    Set slicer = Target.Slicers("ROLE")

You shouldn't need the On Error Resume Next if the pivot table has a slicer named "ROLE". If an error occurs on that line select the slicer and check for leading or trailing spaces in the slicer name in the Name box.

Another correction to your code:

'Check if the selected item is "Planners"
           If selectedSlicerItem.Name = "Planners" Then

 

[Corleone]

Hi
I amended the first bit of the code as suggested
on the second correction you replied with you seem to have just posted what is already in there
here is my updated code with the first amendment - i tried running it both nothing happened

Thanks for looking

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws As Worksheet
Dim slicer As slicer
Dim selectedSlicerItem As SlicerItem

'Specify the name of the worksheet containing the slicer
Set ws = ThisWorkbook.Sheets("GRAPHS") 'Replace "Sheet1" with the actual name of your worksheet

'Check if the sheet contains a slicer with the specified name
Set slicer = Target.Slicers("ROLE")

'Exit if the specified slicer doesn't exist on the sheet
If slicer Is Nothing Then Exit Sub

'Check if the slicer has any selected items
If slicer.SlicerCache.VisibleSlicerItems.Count > 0 Then
'Loop through selected items
For Each selectedSlicerItem In slicer.SlicerCache.VisibleSlicerItems
'Check if the selected item is "Planners"
If selectedSlicerItem.Name = "Planners" Then
'Call the macro for "Planners"
Call Planning_Staff
Exit For 'Exit the loop after finding "Planners"
End If
Next selectedSlicerItem
End If
End Sub

 

[John_w]

on the second correction you replied with you seem to have just posted what is already in there

No, your code was:

'Check if the selected item is "Planners"
           If selectedSlicerItem.Name = "Senior PM" Then

here is my updated code with the first amendment - i tried running it both nothing happened

You don't run the code. The code should run automatically when the pivot table changes as a result of you selecting an item in the slicer. The code should be placed in the ThisWorkbook module.

 

[Corleone]

No, your code was:

'Check if the selected item is "Planners"
           If selectedSlicerItem.Name = "Senior PM" Then

You don't run the code. The code should run automatically when the pivot table changes as a result of you selecting an item in the slicer. The code should be placed in the ThisWorkbook module.

Apologies, My mistake - i had forgotten that i had changed the line to
If selectedSlicerItem.Name = "Planners" Then

anyway - i have moved the code into the Thisworkbook module but it still doesn't activate when i click on the Planners button

 

[John_w]

Do you have a pivot table? And is the slicer connected to it?

 

[Corleone]

There is no pivot table the slicer is connected to a table of data but its not a pivot table

 

[John_w]

Without a pivot table the Workbook_SheetPivotTableUpdate event won't be triggered, so you can't use that.

One might think that the Worksheet_Change event would be a suitable event to detect a change in a table, however it doesn't fire when the table rows are hidden or shown, as a result of you filtering the table, either via table column(s) or slicer(s) linked to the table.

You could try the below Worksheet_Calculate event, however this is only triggered if you have a formula on the sheet which Excel needs to recalculate.

Code in the Sheet module of the sheet containing the table:

Private Sub Worksheet_Calculate()
    Check_Slicer
End Sub

Private Sub Check_Slicer()

    Dim slCache As SlicerCache
    Dim slItem As SlicerItem
    
    On Error Resume Next
    Set slCache = ThisWorkbook.SlicerCaches("Slicer_ROLE")
    On Error GoTo 0
    
    If Not slCache Is Nothing Then
        With slCache
            For Each slItem In .VisibleSlicerItems
                If slItem.Name = "Planners" And slItem.Selected Then
                    Call Planning_Staff
                    Exit For
                End If
            Next
        End With
    End If
    
End Sub

Note that the name of the slicer cache in the above code is "Slicer_ROLE". This is the 'Name to use in formulas' on the slicer's Slicer Settings dialogue.

 

[Corleone]

Youve solved it!!!!

Many thanks for all your help