VBA to change selection on a Power Query Table Slicer

Question

I have been stuck on this all day and would really appreciate some help.

I have a table which I created in Power Query and have loaded this into an Excel worksheet named "Pay Com". To this table, I have added 2 slicers - one for Job and the other for Country. The filters work fine on the table.

I am trying to add VB code where I can change the slicer selections based on named ranges. So I want the Job slicer to be the value in my named range Filter_Job and the Country slicer to be the value in my named range 'Filter_Country'

I am not that proficient in VBA, but I have used code previously to change slicers in pivot tables. However, I cannot get this slicer to work and I don't know why. Is it something to do with the slicer being tied to a table rather than a pivot table? (The Report Connection option is greyed out if that has any implications).

Any guidance would be really appreciated.

I tried a couple of methods after googling this. I tried .SlicerCaches and it didn't like it. I tried .Shapes and it didn't work I have passed the variables and set my variable values. It is the end part where I try to filter the slicer it does not work

This is my current code: The first part of this code is dealing with double clicking within a pivot table, extracting values from the position of the double-clicked cell and pasting those into the named cells. The second part of the code is what I need help with. I want to take the values from these named cells and use them to filter my slicers for a table that has been loaded from Power Query. It seems the table being from Power Query is the issue I am facing?

I have incorporated the code as per your answer @Taller but that still does not seem to work. It needs debugging at this line: countryValue = "|" & Join(Application.Transpose(Range("Filter_NH_Country")), "|") & "|"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim pt As PivotTable
    Dim wsHeatMap As Worksheet
    Dim wsCom As Worksheet

    Dim SI As SlicerItem
    Dim jcValue As String
    Dim countryValue As String
    
    
    ' Set references to the specific worksheets & Table
    Set wsHeatMap = ThisWorkbook.Sheets("NH Map")
    Set wsCom = ThisWorkbook.Sheets("Pay Com")
    
    
    ' Check if only one cell is selected in pivot table
    If Target.Count = 1 Then
        ' Check if the double click happened in a PivotTable
        On Error Resume Next
        Set pt = Target.PivotTable
        On Error GoTo 0
        
        If Not pt Is Nothing And pt.Name = "pvt_Map" Then
            ' This is my specific pivot table
            ' Adjust the logic as needed, Example: Cancel the double click
            Cancel = True
    
    ' Extract values from position of my selected cell
    jcValue = Cells(Target.Row, "D").Value
    countryValue = Cells(10, Target.Column - 1).Value
            
    ' Place the extracted values in the named ranges
    Range("Filter_Job").Value = jcValue
    Range("Filter_Country").Value = countryValue
        
    ' Reset error handling
    On Error GoTo 0
    
    jcValue = Range("Filter_Job").Value
    countryValue = Range("Filter_Country").Value
    
    
    ' Apply filters to slicers in the "Pay Comp" worksheet
    
    ***countryValue = "|" & Join(Application.Transpose(Range("Filter_NH_Country")), "|") & "|"***
    For Each SI In wsCom.SlicerCaches("Slicer_Country1").SlicerItems
        SI.Selected = InStr(1, countryValue, "|" & SI.Name & "|")
    Next
    jobValue = "|" & Join(Application.Transpose(Range("Filter_NH_JC")), "|") & "|"
    For Each SI In wsComp.SlicerCaches("Slicer_Job_Code").SlicerItems
        SI.Selected = InStr(1, jobValue, "|" & SI.Name & "|")
    Next
        
        
        End If
        
    End If


End Sub

Answer 1

• Assuming slicers are named Slicer_Country and Slicer_Job

Microsoft documentation:

SlicerItem.Selected property (Excel)

InStr function

Sub DEMO()
    Dim oSI As SlicerItem
    Dim sFilterCou As String, sFilterJob As String
    sFilterCou = "|" & Join(Application.Transpose(Range("Filter_Country")), "|") & "|"
    For Each oSI In ActiveWorkbook.SlicerCaches("Slicer_Country").SlicerItems
        oSI.Selected = InStr(1, sFilterCou, "|" & oSI.Name & "|")
    Next
    sFilterJob = "|" & Join(Application.Transpose(Range("Filter_Job")), "|") & "|"
    For Each oSI In ActiveWorkbook.SlicerCaches("Slicer_Job").SlicerItems
        oSI.Selected = InStr(1, sFilterJob, "|" & oSI.Name & "|")
    Next
End Sub