0

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?

kicpereniek
  • 47
  • 1
  • 7
  • Please show the **exact** code you used by [edit]ing the question. Also explain what *"doesn't work"* exatcly means because this is not a useful error description. Which errors do you get and in which line? What happens versus what did you expect to happen? – Pᴇʜ Jun 18 '21 at 07:33
  • Dear @Pᴇʜ, thanks for your comment. I added the information you needed to my question. – kicpereniek Jun 18 '21 at 08:09
  • 1
    Try to replace `&Undo` with the polish word of the command bar button. If you don't know the exact name you can write a little procedure looping throungh the controls of `Application.CommandBars("Standard")` and `Debug.Print` the names of the controls: `Dim itm: For Each itm In Application.CommandBars("Standard").Controls: Debug.Print itm.Caption: Next itm` – Pᴇʜ Jun 18 '21 at 08:15
  • Not sure if it's static on all versions of Excel, but the control's Id parameter seems to stay the same whatever the locale. For example `Application.CommandBars("Standard").Controls("&Undo").Id` in English is the same as as `Application.CommandBars("Standard").Controls("Ann&uler").Id` in French (128). – Vincent G Jun 18 '21 at 08:20
  • You should be able to get the undo control directly using the [FindControl](https://learn.microsoft.com/fr-fr/office/vba/api/office.commandbars.findcontrol) member function: `Application.CommandBars.FindControl(ID:=128).List(1)` – Vincent G Jun 18 '21 at 08:27
  • @VincentG Obviously that doesn't work always (or not in all Excel versions) and the `Controls("&Undo")` was added as fallback if `FindControl(ID:=128)` fails? – Pᴇʜ Jun 18 '21 at 08:33
  • @Pᴇʜ I replaced `&Undo` with `&Cofnij`, which is the Polish equivalent. The changed code line is now: `If sLastUndoStackItem = "" Then sLastUndoStackItem = Application.CommandBars("Standard").Controls("&Cofnij").List(1)` However, it's still not working. When I select an item from the pivot table slicer, the regular table is not affected in Polish Excel. As mentioned in the beginning, the solution works perfectly in English version. Do you have any idea what else may cause this issue? – kicpereniek Jun 18 '21 at 09:50
  • @kicpereniek the `&` might need to be in another position. Write a function with the following code `Dim itm: For Each itm In Application.CommandBars("Standard").Controls: Debug.Print itm.Caption: Next itm` and check the immediate window. Look for `Cofnij` and check the position of the `&` sign. – Pᴇʜ Jun 18 '21 at 09:53
  • @Pᴇʜ the`&` position is correct. In the Debug.Print output I have `&Cofnij` – kicpereniek Jun 18 '21 at 09:57

1 Answers1

2

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • After running `Sub Investigate()` the message box pops up: `Run-time error '-2147467259 (80004005): Automation error Nieokreslony blad.` The last part translates to "undefined error". In the immediate window the output is: `UndoControl ID 128 not found`. I also added the code `If sLastUndoStackItem = "" Then MsgBox ....` after the code you highlighted but still nothing happens - the message box doesn't show up. – kicpereniek Jun 18 '21 at 11:13
  • 1
    Ah I think we are getting close. Do a `Debug.Print sLastUndoStackItem` there. Probably the strings in `If sLastUndoStackItem = "Filter" Or sLastUndoStackItem = "Slicer Operation" Then` need to be translated too into polish. – Pᴇʜ Jun 18 '21 at 11:24
  • 1
    it works perfectly. After debugging I added the Polish translation of 'Slicer Operation', which is `Operacja fragmentatora`. Now the code line is `If sLastUndoStackItem = "Filter" Or sLastUndoStackItem = "Slicer Operation" Or sLastUndoStackItem = "Operacja fragmentatora" Then` It fully resolves the issue, selecting an item from pivot table slicer changes the filtering of the regular table as well. Thank you very much Pᴇʜ for your help on this. Wishing you all the best :) – kicpereniek Jun 18 '21 at 11:40