1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Change Pivot Table Selection via Drpdown

Discussion in 'VBA Macros' started by Dokat, Mar 7, 2019.

  1. Dokat

    Dokat Member

    Messages:
    315
    Hi,

    I have a pivot table in "Inno" worksheet that i'd like to control from drop down box in "Summary" sheet. I created a name range for the dropdown box called "Region"I have below code however it is not correctly running nor giving me any error message. Can you please help?

    Code (vb):

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim strField As String
    Dim strField2 As String
    strField = "Region"
    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False
        If Target.Address = ThisWorkbook.Sheets("Summary").Range("Region").Address Then
         
            Set ws = ThisWorkbook.Sheets("Inno")
                For Each pt In ws.PivotTables("PivotTable1")
                    With pt.PageFields(strField)
                        For Each pi In .PivotItems
                            If pi.Value = Target.Value Then
                                .CurrentPage = Target.Value
                                Exit For
                               Else
                                .CurrentPage = "(All)"
                            End If
                        Next pi
                    End With
                Next pt
    '        Next ws
     
        End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub
     
    Last edited: Mar 7, 2019
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    You shouldn't use VBA for this.

    Just create copy of PivotTable, but just with filter field, and have hidden sheet with slicer that connects the two pivot.

    This method has added benefit of allowing for multiple selection, search of dropdown value etc.

    See attached.

    Control has the filter dropdown. Pivot has the pivot with data. Slicer sheet is shown, but can be hidden.

    Attached Files:

    Thomas Kuriakose and Dokat like this.
  3. Dokat

    Dokat Member

    Messages:
    315
    Thank you. For this dashboard i will have to use drop down. Unfortunately i cant do the slicers even tho its a much easier solution. Is there a way to do this via dropdown?
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Did you look? It's dropdown that you'd use. Slicer is hidden and not used in this set up. It's there only to connect two pivots.
  5. Dokat

    Dokat Member

    Messages:
    315
    Yes, slicer works great and a very good solution. However i will need to do a dropdown for this but i cant get VBA code to work.

    Thanks
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    ... It's dropdown that you use for control/filtering. Not the slicer.

    upload_2019-3-7_14-52-1.png

    If you need help with code and absolutely need to use named range and data validation... I'd suggest uploading sample workbook.
  7. Dokat

    Dokat Member

    Messages:
    315
    Yes i need help with the vba code. Iam using named range and data validation under summary tab. Inno tab has the actual pivot table and the source data. I need to be able to change the selection in pivot table with drop down cells in b6 and c6 in Summary sheet and please see below link for the upload.

    https://ufile.io/0use0
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Few issues here.

    1. There is only 1 pivot in the sheet. So there is no need to loop PivotTables collection.
    2. You are trying to loop on PivotTable collection when you've already specified the PivotTable object.
    3. You have to be careful with setting .CurrentPage as this property will accept ANY values.

    Last but not least... You DO NOT have PivotField named "Region" in your pivot table. It's named "Geography" in your set up...

    So taking all of the above into consideration... something like below.
    Code (vb):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strField As String
    Dim pvI As PivotItem
    Dim fFlag As Boolean: fFlag = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Not Intersect(Target, [B6]) Is Nothing Then
        strField = "Geography"
        With Sheets("Inno").PivotTables(1)
            For Each pvI In .PivotFields(strField).PivotItems
                If pvI.Value = Target.Value Then
                    .PageFields(strField).CurrentPage = Target.Value
                    fFlag = True
                    Exit For
                End If
            Next
            If Not fFlag Then
                .PageFields(strField).CurrentPage = "(All)"
                MsgBox "There is no " & Target.Value & " in current context"
            End If
        End With
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub
     
    Thomas Kuriakose and Dokat like this.
  9. Dokat

    Dokat Member

    Messages:
    315
    Thank you! Code worked when i change the drop down in cell B6 however when i change C6 it doesnt. Can you please help.

    I modified the code below but getting "end without with error"

    Code (vb):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strField As String
    Dim pvI As PivotItem
    Dim strField1 As String
    Dim pvI1 As PivotItem
    Dim fFlag As Boolean: fFlag = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Not Intersect(Target, [B6]) Is Nothing Then
        strField = "Geography"
        With Sheets("Inno").PivotTables(1)
            For Each pvI In .PivotFields(strField).PivotItems
                If pvI.Value = Target.Value Then
                    .PageFields(strField).CurrentPage = Target.Value
                End If
            End With
                     
    If Not Intersect(Target, [C6]) Is Nothing Then
        strField1 = "Product"
        With Sheets("Inno").PivotTables(1)
            For Each pvI1 In .PivotFields(strField1).PivotItems
                If pvI1.Value = Target.Value Then
                    .PageFields(strField1).CurrentPage = Target.Value
                    fFlag = True
                    Exit For
                End If
            Next
            If Not fFlag Then
                .PageFields(strField1).CurrentPage = "(All)"
                MsgBox "There is no " & Target.Value & " in current context"
            End If
        End With
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub
     
    Last edited: Mar 8, 2019
  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    You have several issues.

    1. You are missing closing End If for first if statement.
    2. You are not resetting fFlag.
    3. You are missing Next for For Each pvI loop.

    Personally I'd just combine the two. Since code structure would be identical. Only 2 things would be different between two operation (strField & Target).

    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strField As String
    Dim pvI As PivotItem
    Dim fFlag As Boolean: fFlag = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Not Intersect(Target, [B6:C6]) Is Nothing Then
        strField = IIf(Target.Address = "$B$6", "Geography", "Product")
        With Sheets("Inno").PivotTables(1)
            For Each pvI In .PivotFields(strField).PivotItems
                If pvI.Value = Target.Value Then
                    .PageFields(strField).CurrentPage = Target.Value
                    fFlag = True
                End If
            Next
            If Not fFlag Then
                .PageFields(strField).CurrentPage = "(All)"
                MsgBox "There is no " & Target.Value & " in current context"
            End If
        End With

    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub
     
    However, there is additional issue. Not all items in your dropdown match your PivotItem. You must match EXACTLY, including case, or it will not find the match.
    Thomas Kuriakose and Dokat like this.
  11. Dokat

    Dokat Member

    Messages:
    315
    Thanks for your response. I modified the code and the dropdown range to match PivotItem but it is still not working. I uploaded sample file below.

    https://ufile.io/3z17r
  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Well... Purex HDD is NOT exact match for PUREX HDD. As I noted, case has to match as well.

    As well, you have the code in Standard Module. Code should go in Worksheet Module for "Summary" sheet.
    Thomas Kuriakose and Dokat like this.
  13. Dokat

    Dokat Member

    Messages:
    315
    Thank
    Thank you! It worked!
  14. Dokat

    Dokat Member

    Messages:
    315
    Is there anyway for dropdown box to control 2 pivot tables if they are linked? Something like below?

    Code (vb):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strField As String
    Dim pvI As PivotItem
    Dim fFlag As Boolean: fFlag = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Not Intersect(Target, [B6:C6]) Is Nothing Then
      strField = IIf(Target.Address = "$B$6", "Geography", "Product")
      With Sheets("Inno").PivotTables(1)
      For Each pvI In .PivotFields(strField).PivotItems
      If pvI.Value = Target.Value Then
      .PageFields(strField).CurrentPage = Target.Value
      fFlag = True
      End If
      Next
     
      With Sheets("Inno").PivotTables(1)
      For Each pvI In .PivotFields(strField).PivotItems
      If pvI.Value = Target.Value Then
      .PageFields(strField).CurrentPage = Target.Value
      fFlag = True
      End If
      Next
      If Not fFlag Then
      .PageFields(strField).CurrentPage = "(All)"
      MsgBox "There is no " & Target.Value & " in current context"
      End If
      End With
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub
     
    Thanks
    Last edited: Mar 12, 2019
  15. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    If it's linked, use hidden slicer to connect two pivots. If one is filtered via code, 2nd will be filtered as well.

    If slicer can't be used (i.e. Excel version is 2007 or older)... You'll need to upload sample workbook. There are many things that can go wrong, when trying to manipulate two pivots without built-in slicer/model. Which code will have to accommodate for.
  16. Dokat

    Dokat Member

    Messages:
    315

    Hi,

    I am trying to control pivottable1 , pivottable2, pivottable2 under "Inno" tab with a dropdown under "Summary" tab without a slicer.

    Below is the link to the sample file.

    https://ufile.io/i45kx
  17. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Never a good idea to stack pivot tables in same page...

    Don't have time today to look at it, but will see if I have time tomorrow.
  18. Dokat

    Dokat Member

    Messages:
    315
    ok thanks
  19. Dokat

    Dokat Member

    Messages:
    315
    I modified the code however keeping getting "Compile Error: End With without With" error. Can you please help

    Code (vb):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strField As String
    Dim strField1 As String
    Dim pvI As PivotItem
    Dim pvI1 As PivotItem
    Dim fFlag As Boolean: fFlag = False
    Dim fFlag1 As Boolean: fFlag = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Not Intersect(Target, [B6:C6]) Is Nothing Then
        strField = IIf(Target.Address = "$B$6", "Geography", "Product")
        With Sheets("Inno").PivotTables(1)
            For Each pvI In .PivotFields(strField).PivotItems
                If pvI.Value = Target.Value Then
                    .PageFields(strField).CurrentPage = Target.Value
                    fFlag = True
                End If
            Next
            If Not fFlag Then
                .PageFields(strField).CurrentPage = "(All)"
                MsgBox "There is no " & Target.Value & " in current context"
            End If
           
             Application.EnableEvents = False
                strField1 = IIf(Target.Address = "$B$6", "Geography", "Product")
        With Sheets("Inno").PivotTables(2)
            For Each pvI1 In .PivotFields(strField1).PivotItems
                If pvI1.Value = Target.Value Then
                    .PageFields(strField1).CurrentPage = Target.Value
                    fFlag1 = True
                      Application.EnableEvents = True
                End If
              End With
                               
        End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub
     
  20. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    There's 3 pivot tables in your "Inno" sheet. Which one should be controlled? Or is it all 3?
  21. Dokat

    Dokat Member

    Messages:
    315
    Yes, all 3.

    Thanks
  22. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Then this.

    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strField As String
    Dim pvI As PivotItem, pvT As PivotTable
    Dim fFlag As Boolean: fFlag = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Not Intersect(Target, [B6:C6]) Is Nothing Then
        strField = IIf(Target.Address = "$B$6", "Geography", "Product")
        For Each pvT In Sheets("Inno").PivotTables
            With pvT
                For Each pvI In .PivotFields(strField).PivotItems
                    If pvI.Value = Target.Value Then
                        .PageFields(strField).CurrentPage = Target.Value
                        fFlag = True
                    End If
                Next
                If Not fFlag Then
                    .PageFields(strField).CurrentPage = "(All)"
                    MsgBox "There is no " & Target.Value & " in current context"
                End If
            End With
        Next
        fFlag = False
    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub
    Dokat likes this.
  23. Dokat

    Dokat Member

    Messages:
    315
    It works. Thank you
  24. Dokat

    Dokat Member

    Messages:
    315
    Code is sometimes randomly not working. I cant change the pivot filters via drop down. I make sure the named ranges matches the drop down list. Not sure whats causing this issue? Usually restarting the computer fixes the issue.

    Thanks
    Last edited: Mar 14, 2019
  25. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Chances are that you had some error generated during code execution. Or code was prematurely terminated.

    Add error trap to code.
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strField As String
    Dim pvI As PivotItem, pvT As PivotTable
    Dim fFlag As Boolean: fFlag = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    On Error GoTo ErrHandle:
    If Not Intersect(Target, [B6:C6]) Is Nothing Then
        strField = IIf(Target.Address = "$B$6", "Geography", "Product")
        For Each pvT In Sheets("Inno").PivotTables
            With pvT
                For Each pvI In .PivotFields(strField).PivotItems
                    If pvI.Value = Target.Value Then
                        .PageFields(strField).CurrentPage = Target.Value
                        fFlag = True
                    End If
                Next
                If Not fFlag Then
                    .PageFields(strField).CurrentPage = "(All)"
                    MsgBox "There is no " & Target.Value & " in current context"
                End If
            End With
        Next
        fFlag = False
    End If
    ErrHandle:
    If Err.Number <> 0 Then
        MsgBox "Something went wrong, comment out error On Error GoTo... and ErrHandle: section of code and step through the code to debug.
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub

Share This Page