• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is this possible with Slicers? Referencing a cell.

C

Christof

Member
Hi guys,

Based on my code below, is it possible to use the value in cell $E$12 for the slicer, but it updates if the value in cell $E$11 is changed ($E$12 is a looked up value based on input made in cell $E$11)?

Code: 
Dim sc4 As SlicerCache, si4 As SlicerItem
  Set sc4 = ThisWorkbook.SlicerCaches("Slicer_Main")
  If Not Intersect(Target, [E12]) Is Nothing Then
        If Target.Value = "(All)" Then
            sc4.ClearAllFilters
        Else
            sc4.ClearAllFilters
            For Each si4 In sc4.SlicerItems
                If Target.Value = si4.Name Then
                    si4.Selected = True
                Else
                    si4.Selected = False
                End If
            Next
        End If
    End If


Thanks
Christof
 
Yes. I assume you have the code in the worksheet_change event of the sheet.

1. Change Intersect function reference to [E11]. So that it triggers when E11 is changed.

2. For rest of the code replace 'Target' with Target.Offset(1,). This will reference cell 1 row below the Target (i.e. E12).
 
Could I also ask a quick question on my code to update my slicer.
It seems to take a long time, and was wondering if this code is the best way of doing it?
Basically E11 is a dropdown, so when that changes I need my slicer(s) to update with that new value.. but at the minute the loop through seems to take ages because I have a lot of values in my dropdown menu.
Any help would be greatly appreciated :)
 
Below is my code.. the first part changes the layouts depending on how the user wants to see it all... and then the slicer bits at the bottom drive the selections.


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim KeyCells As Range
    Set KeyCells = Range("g3:M3")
       
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        On Error GoTo clean_up
        Application.Calculation = xlManual
        Application.StatusBar = "Hi " & Application.UserName & "! Your report will be ready in a few seconds..."
        Application.EnableEvents = False
        'Set the Variables to be used
        Dim pt1 As PivotTable
        Dim pt2 As PivotTable
        Dim pt3 As PivotTable
        Dim pt4 As PivotTable
        Dim pt7 As PivotTable
     
       
        Dim Field1 As PivotField
        Dim Field2 As PivotField
        Dim Field3 As PivotField
        Dim Field4 As PivotField
        Dim Field5 As PivotField
        Dim Field6 As PivotField
        Dim Field7 As PivotField
        Dim Field8 As PivotField
        Dim Field9 As PivotField
        Dim Field10 As PivotField
        Dim Field21 As PivotField
        Dim Field22 As PivotField
        Dim Field23 As PivotField
        Dim Field24 As PivotField
        Dim Field25 As PivotField
       
       
       
        Dim NewCat1 As String
        Dim NewCat2 As String
        Dim NewCat3 As String
        Dim NewCat4 As String
        Dim NewCat5 As String
        Dim NewCat6 As String

               
       
        Dim pi As PivotItem
        Dim Yeartype As String

       
        Yeartype = Range("h3").Value
       
         NewCat1 = Range("e5").Value
         NewCat2 = Range("g3").Value
         NewCat3 = Range("g3").Value - 1
         NewCat4 = "Okay"
         NewCat5 = Range("e7").Value
         NewCat6 = Range("e9").Value
   
       
        '-----Pivot1-----
   
        Set pt1 = PivotTables("PivotTable1")
        Set Field1 = pt1.PivotFields("Brand")
        Set Field2 = pt1.PivotFields("New Product Group")
        Set Field3 = pt1.PivotFields("Country")
        Set Field4 = pt1.PivotFields("Ignore")
        pt1.ManualUpdate = True

        With pt1.PivotFields(Yeartype)
            .Orientation = xlRowField
            .Position = 1
        End With
        If Yeartype = "Financial Year" Then
            pt1.PivotFields("Calendar Year").Orientation = xlHidden
            Set Field5 = pt1.PivotFields("Financial Year2")
        Else
            pt1.PivotFields("Financial Year").Orientation = xlHidden
            Set Field5 = pt1.PivotFields("Calendar Year2")
        End If
       
        pt1.AllowMultipleFilters = True
       
        pt1.ClearAllFilters
       

        Field1.CurrentPage = NewCat1
        Field2.CurrentPage = NewCat5
        Field3.CurrentPage = NewCat6
        Field4.CurrentPage = NewCat4
       
       
        For Each pi In Field5.PivotItems
            If Not ((pi.Name = NewCat2) Or (pi.Name = NewCat3)) Then
                pi.Visible = False
            End If
        Next
        pt1.ManualUpdate = False
        pt1.RefreshTable

        '-----Pivot2-----
   
        Set pt2 = PivotTables("PivotTable2")
        Set Field6 = pt2.PivotFields("Brand")
        Set Field7 = pt2.PivotFields("New Product Group")
        Set Field8 = pt2.PivotFields("Country")
        Set Field9 = pt2.PivotFields("Ignore")
        pt2.ManualUpdate = True

        With pt2.PivotFields(Yeartype)
            .Orientation = xlRowField
            .Position = 1
        End With
        If Yeartype = "Financial Year" Then
            pt2.PivotFields("Calendar Year").Orientation = xlHidden
            Set Field10 = pt2.PivotFields("Financial Year2")
        Else
            pt2.PivotFields("Financial Year").Orientation = xlHidden
            Set Field10 = pt2.PivotFields("Calendar Year2")
        End If
       
        pt2.AllowMultipleFilters = True
       
        pt2.ClearAllFilters
       

        Field6.CurrentPage = NewCat1
        Field7.CurrentPage = NewCat5
        Field8.CurrentPage = NewCat6
        Field9.CurrentPage = NewCat4
       
       
        For Each pi In Field10.PivotItems
            If Not ((pi.Name = NewCat2) Or (pi.Name = NewCat3)) Then
                pi.Visible = False
            End If
        Next
        pt2.ManualUpdate = False
        pt2.RefreshTable

     
        '-----Pivot7-----
   
        Set pt7 = PivotTables("PivotTable7")
        Set Field21 = pt7.PivotFields("Brand")
        Set Field22 = pt7.PivotFields("New Product Group")
        Set Field23 = pt7.PivotFields("Country")
        Set Field24 = pt7.PivotFields("Ignore")
       
        pt7.ManualUpdate = True

        If Yeartype = "Financial Year" Then

            Set Field25 = pt7.PivotFields("Financial Year2")
        Else

            Set Field25 = pt7.PivotFields("Calendar Year2")
        End If
       
        pt7.AllowMultipleFilters = True
       
        pt7.ClearAllFilters
       

        Field21.CurrentPage = NewCat1
        Field22.CurrentPage = NewCat5
        Field23.CurrentPage = NewCat6
        Field24.CurrentPage = NewCat4
        Field25.CurrentPage = NewCat2
       

        pt7.ManualUpdate = False
        pt7.RefreshTable
       
  End If
  Dim sc1 As SlicerCache, si1 As SlicerItem
  Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Brand3")
  If Not Intersect(Target, [E5]) Is Nothing Then
        If Target.Value = "(All)" Then
            sc1.ClearAllFilters
        Else
            sc1.ClearAllFilters
            For Each si1 In sc1.SlicerItems
                If Target.Value = si1.Name Then
                    si1.Selected = True
                Else
                    si1.Selected = False
                End If
            Next
        End If
    End If


  Dim sc2 As SlicerCache, si2 As SlicerItem
  Set sc2 = ThisWorkbook.SlicerCaches("Slicer_New_Product_Group3")
  If Not Intersect(Target, [E7]) Is Nothing Then
        If Target.Value = "(All)" Then
            sc2.ClearAllFilters
        Else
            sc2.ClearAllFilters
            For Each si2 In sc2.SlicerItems
                If Target.Value = si2.Name Then
                    si2.Selected = True
                Else
                    si2.Selected = False
                End If
            Next
        End If
    End If


  Dim sc3 As SlicerCache, si3 As SlicerItem
  Set sc3 = ThisWorkbook.SlicerCaches("Slicer_Country3")
  If Not Intersect(Target, [E9]) Is Nothing Then
        If Target.Value = "(All)" Then
            sc3.ClearAllFilters
        Else
            sc3.ClearAllFilters
            For Each si3 In sc3.SlicerItems
                If Target.Value = si3.Name Then
                    si3.Selected = True
                Else
                    si3.Selected = False
                End If
            Next
        End If
    End If


        Application.Calculation = xlAutomatic
        Application.StatusBar = "All done!"


clean_up:
       Application.EnableEvents = True

End Sub
 
Only way to speed this up is to use OLAP based pivot table. It will allow you to use array() to set list of visible item(s) in one shot, without looping.

Also, if I recall, you had quite a bit of data that was dependent on PivotTable. That would slow things down as well. Since it requires pivot table to finish refreshing, before calculation can be initiated.

If you have access to PowerQuery and PowerPivot, use these to generate your report using DAX instead of using traditional format. Has much better compression and fast aggregation/slicing of data.
 
Thanks Chihiro.
I've done a little bit of research on how to do the PowerPivot stuff now.
Would I easily be able to replace my current PivotTables and replace them with PowerPivotTables, and keep the VBA code, so long as I rename my PowerPivots the same. eg: PivotTable1 = pt1, PivotTable2 = pt2, etc..?
 
No you will have to rewrite code. Syntax for OLAP based pivot fields and items are quite different from regular pivot.
 
You must log in or register to reply here.
Back
Top