CODEWITHSUNDEEP
excelvbapivot-table
kicpereniek
kicpereniek

Reputation: 47

How to link a Table and a Pivot Table using Slicers in non-English Excel versions?

I'm trying to link slicers from a pivot table with regular table. So in the end when I select something in pivot table slicer it should filter the data in the regular table as well.

So far I followed the instructions from this solution: How to link a Table and a Pivot Table using Slicers in Excel?

Below please find the exact code I'm using in my workbook:

    Option Explicit

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim sLastUndoStackItem As String
Dim sc          As SlicerCache
Dim si          As SlicerItem
Dim vItems      As Variant
Dim i           As Long
Dim lo          As ListObject
Dim lc          As ListColumn
Dim sTest       As String

Const sPivot As String = "pivot1" '<= Change name as appropriate
Const sTable As String = "Table1" '<= Change name as appropriate

If Target.Name = sPivot Then
    On Error Resume Next 'in case the undo stack has been wiped or doesn't exist
    sLastUndoStackItem = Application.CommandBars(14).FindControl(ID:=128).List(1) 'Standard Commandbar, undo stack
    'The above line doesn't seem to work in my version of O365 so we'll use the English language backup
    If sLastUndoStackItem = "" Then sLastUndoStackItem = Application.CommandBars("Standard").Controls("&Undo").List(1)
    On Error GoTo 0

    If sLastUndoStackItem = "Filter" Or sLastUndoStackItem = "Slicer Operation" Then

        Set lo = Sheets("Sheet1").Range(sTable).ListObject

        For Each sc In ActiveWorkbook.SlicerCaches
            On Error Resume Next
            sTest = sc.PivotTables(1).Name
            On Error GoTo 0
            If sTest = sPivot Then
                Set lc = lo.ListColumns(sc.SourceName)
                If sc.FilterCleared Then
                    lo.Range.AutoFilter Field:=lc.Index
                Else
                    ReDim vItems(1 To 1)
                    For Each si In sc.SlicerItems
                        If si.Selected Then
                            i = i + 1
                            ReDim Preserve vItems(1 To i)
                            vItems(i) = si.Name
                        End If
                    Next si

                    lo.Range.AutoFilter Field:=lc.Index, Criteria1:=vItems, Operator:=xlFilterValues
                    ReDim vItems(1 To 1)
                End If
            End If
        Next sc
    End If
End If 
End Sub

With this code I'm able to select an item from the pivot table slicer, and the regular table filters as well. This works perfectly in English excel version but it doesn't work in other languages, like Polish. It means that when I select an item from the pivot table slicer in Polish Excel version, only pivot table filters, and regular table is not affected. I don't get any errors from the VBA. I'm looking for a solution which would work regardless of the language version.

I suppose the issue may be connected with the line:

If sLastUndoStackItem = "" Then sLastUndoStackItem = Application.CommandBars("Standard").Controls("&Undo").List(1)

I'm using Excel 2016.

Can anybody help me on this?

Upvotes: 0

Views: 971

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Try to investigate a bit to find wich part of your code does not work as expected.

Sub Investigate()
    Dim Bar As CommandBar
    Dim UndoControl As CommandBarControl
    
    Set UndoControl = Application.CommandBars(14).FindControl(ID:=128)
    If UndoControl Is Nothing Then
        Debug.Print "UndoControl ID 128 not found"
        
        Set UndoControl = Application.CommandBars("Standard").Controls("&Cofnij")
        If UndoControl Is Nothing Then
            Debug.Print "UndoControl &Cofnij not found"
            Exit Sub
        End If
    End If

 

    If UndoControl.ListCount > 0 Then
        Debug.Print UndoControl.List(1)
    Else
        Debug.Print "List is empty"
    End If
End Sub

Check what you get as output.

After this code

On Error Resume Next 'in case the undo stack has been wiped or doesn't exist
sLastUndoStackItem = Application.CommandBars(14).FindControl(ID:=128).List(1) 'Standard Commandbar, undo stack
'The above line doesn't seem to work in my version of O365 so we'll use the English language backup
If sLastUndoStackItem = "" Then sLastUndoStackItem = Application.CommandBars("Standard").Controls("&Undo").List(1)
On Error GoTo 0

you should check if stack items were found at all. So if nothing was found you get notified instead of a silent "nothing happens".

If sLastUndoStackItem = "" Then
    MsgBox "No stack item found."
    Exit Sub
End If

Upvotes: 2

Related Questions