(SOLVED) Slicers for listObject (Table)

[storm8]

Hello,

slicers (introduced in excel 2010) are great feature, however they can be used only for pivot Tables, by default you cannot use them on listObject (table). But with my solution you can...

1. Create empty pivot table for desired listobject (preferably on other sheet)
2. create desired slicers with the pivot table, move them to sheet with listObject and place as you wish (I suggest using first row that should be empty and row.height should match slicer.height)
3. on worksheet with pivotTable create worksheet event: Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
4. Code for worksheet event is following

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
    Dim slc As SlicerCache
    Dim loX As ListObject
    Dim aWb As Workbook
    Set aWb = ActiveWorkbook
    Dim pt As PivotTable
    Set pt = Target
    
    Application.ScreenUpdating = False
    
    Set loX = Sheets("studenti").ListObjects("t_studenti") 'your listObjct
    
    'call core functio for each slicer you created (in my case I have 4 of them)
    Call FilterSet_Core("Slicer_a", loX, 1)
    Call FilterSet_Core("Slicer_b", loX, 2)
    Call FilterSet_Core("Slicer_c", loX, 3)
    Call FilterSet_Core("Slicer_d", loX, 4)
    
    Application.ScreenUpdating = True
End Sub


Private Sub FilterSet_Core(ByRef scN As String, ByRef lo As ListObject, fld As Integer)
    'fld is colum number to which slicer reffers
    Dim iSc   As Integer
    Dim rw    As Integer
    Dim arrI  As Variant
    Dim arrS  As Variant
    Dim iTrue As Integer
    Dim cnt   As Integer
    Dim sc    As SlicerCache
    Dim capt As String
    
    Set sc = ActiveWorkbook.SlicerCaches(scN)
    iSc = sc.SlicerItems.Count
    
    iTrue = 0
    For rw = 1 To iSc
        If sc.SlicerItems(rw).Selected = True Then
            iTrue = iTrue + 1
        End If
    Next rw
    
    If iTrue = iSc Then 'all itemms on slicer are selected, clear filter
        lo.Range.AutoFilter Field:=fld
    Else 'select items to filter
        ReDim arrI(1 To iTrue)
        cnt = 0
        For rw = 1 To iSc
            If sc.SlicerItems(rw).Selected = True Then
                cnt = cnt + 1
                capt = sc.SlicerItems(rw).Caption
                If capt = "(blank)" Then capt = "=" 'fix blanks
                arrI(cnt) = capt
            End If
        Next rw
        'filter items
        lo.Range.AutoFilter Field:=fld, Criteria1:=arrI, Operator:=xlFilterValues
    End If


End Sub

This should filter your listObject

Now to clear the filters use follwing code placed anywhere

Sub ClearFilters()


    Dim loIM As ListObject
    Dim rw As Integer
    Dim aSU As Boolean
    Dim aEE As Boolean
    Dim aSh As Worksheet
    Dim w As Workbook
    
    Set w = ActiveWorkbook
    Set aSh = ActiveSheet
    
    With Application 'rem and set screenUpdating + enableEvents
        aSU = .ScreenUpdating
        aEE = .EnableEvents
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    'clear filter on 1st table
    Set loIM = aSh.ListObjects(1)

    With loIM
        For rw = 1 To .ListColumns.Count
            .Range.AutoFilter Field:=rw
        Next rw
    End With

    Set loIM = Nothing
    
    Dim sc As SlicerCache
    Dim scC As Integer
    Dim X As String


    For Each sc In w.SlicerCaches
        X = sc.Slicers(1).Parent.name
        If X = aSh.name Then
            sc.ClearManualFilter
        End If
    Next sc
    
    Set w = Nothing
    Set sc = Nothing
    
    With Application
        .ScreenUpdating = aSU
        .EnableEvents = aEE
    End With
End Sub

In my project the code is subject to various cases and is divided into classes etc, so this very piece I put together might not work 100% but if you are interested you should get the idea how I got there. So these were the core fuctions, now optional extras:

What I have:
3 ribbon buttons: show; hide; clear

show:
will display the slicers. first row of sheet is by default hidden, show will unhide first row and display slicers with

    For i = 1 To ActiveSheet.Shapes.Count
        ActiveSheet.Shapes(i).Visible = msoTrue
    Next i

I also have a table whch contains position of each slicer and on "show" they are placed to their correct positions in case user moves them by accident

hide:
hide them back and hide 1st row

    For i = 1 To ActiveSheet.Shapes.Count
        ActiveSheet.Shapes(i).Visible = msoFalse
    Next i
    ActiveSheet.Rows(1).EntireRow.Hidden = True

clear:
call clear function as described above


Hope someone would be interested and find this useful. I welcome any comments and suggestions.

 

[kooijman599]

Hi! I've got a question. I saw your code and you're saying you have a solution to create an empty pivot table, based on a listobject. But I can't see where that part of the code is.

Could you help me? I've three Listobjects in one worksheet. Now I want a button which will create a pivottable based on the data of the ListObjects. Do you maybe know how to do it?

 

[storm8]

for excel 2010 its this

Sub addPivos()
    
    Dim aWb As Workbook
    Set aWb = ActiveWorkbook
    
    aWb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "table1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="Sheet1!R14C4", TableName:="PivotTable3", DefaultVersion:= _
        xlPivotTableVersion14


    aWb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "table2", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="Sheet1!R13C14", TableName:="PivotTable4", DefaultVersion:= _
        xlPivotTableVersion14


    aWb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "table3", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="Sheet1!R14C19", TableName:="PivotTable5", DefaultVersion:= _
        xlPivotTableVersion14
End Sub

table names are table1, table2 and table3, also change destination as desired

 

[kooijman599]

Thanks for your reply! My description wasn't clear enough: sorry!
Because, I now have a file with three worksheets. The first two worksheets contains each one Listobjects. The third one contains one Pivot table with the information of all the Listobject together. My macro cycle through the first two Worksheets: looking for data to use for the pivot table. However, I want both Listobject in the same worksheet. So I want the macro to cycle through Listobjects in just one worksheet.

Check my XLS-file!

How can I arrange this?