• 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.

Change Pivot Table Selection via Drpdown

Dokat

Member
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:
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:

Chihiro

Excel Ninja
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.
 

Attachments

Dokat

Member
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.
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?
 

Chihiro

Excel Ninja
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.
 

Dokat

Member
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
 

Chihiro

Excel Ninja
... 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.
 

Dokat

Member
... It's dropdown that you use for control/filtering. Not the slicer.

View attachment 58531

If you need help with code and absolutely need to use named range and data validation... I'd suggest uploading sample workbook.
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
 

Chihiro

Excel Ninja
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:
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
 

Dokat

Member
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:
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
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:
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:

Chihiro

Excel Ninja
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:
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.
 

Dokat

Member
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:
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.
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
 

Chihiro

Excel Ninja
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.
 

Dokat

Member
Thank
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.
Thank you! It worked!
 

Dokat

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

Code:
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:

Chihiro

Excel Ninja
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.
 

Dokat

Member
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.

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
 

Chihiro

Excel Ninja
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.
 

Dokat

Member
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.
I modified the code however keeping getting "Compile Error: End With without With" error. Can you please help

Code:
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
 

Chihiro

Excel Ninja
Then this.

Code:
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

Member
Then this.

Code:
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
It works. Thank you
 

Dokat

Member
Then this.

Code:
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
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:

Chihiro

Excel Ninja
Chances are that you had some error generated during code execution. Or code was prematurely terminated.

Add error trap to code.
Code:
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
 
Top