none
Linking Pivot Tables to Combox in Userform RRS feed

  • Question

  • Hello,

    I have found this code

    Sub FillComboxBox()

        Dim sheet As Worksheet
        Dim pt As PivotTable
        Dim ptField As PivotField
        Dim piField As PivotField
       
        Set sheet = ThisWorkbook.Worksheets("Pivot Tables")
        Set pt = sheet.PivotTables("PivotTable5")
        Set ptField = pt.PivotFields("Account Executives")
        Set piField = pt.PivotFields("Branch Chief")
        Dim item As PivotItem
        Dim index As Integer
        index = 1
        For Each item In ptField.PivotItems
            Me.ComboBox1.AddItem item.Name
        Next item
        For Each item In piField.PivotItems
            Me.ComboBox1.AddItem item.Name
        Next item
            
          
       
        
        Me.ComboBox1.AddItem "(All)"
       
    End Sub
    Private Sub ComboBox1_Change()
        Dim sheet As Worksheet
        Dim pt As PivotTable
        Dim ptField As PivotField
        Dim piField As PivotField
       
        Set sheet = ThisWorkbook.Worksheets("Pivot Tables")
        Set pt = sheet.PivotTables("PivotTable5")
        Set ptField = pt.PivotFields("Account Executives")
        Set piField = pt.PivotFields("Branch Chiefs")
      
        ptField.CurrentPage = Me.ComboBox1.Value
        piField.CurrentPage = Me.ComboBox1.Value
    End Sub

    It will sort the pivot tables however I can not get the name selected to match the name in the combox I am calling the event from.  The name of it is cmbsearch.  how do I get this code to work so the text in combobox1=cmbsearch.

    Thanks,

    Keri

    Tuesday, August 29, 2017 3:11 PM

All replies

  • Hi Lavenderchan,

    ->It will sort the pivot tables however I can not get the name selected to match the name in the combox I am calling the event from. 

    What do you want to do? I think CurrentPage's function is more like filtering the pivot table but not sorting the pivot table.

    What is the cmbsearch? Please provide details about it.

    It seems that you want to us a combine of "Account Executives" field and "Branch Chiefs" field to filter the pivot table.

    If so, I would suggest you create two combo boxes, one for Account Executives and one for Branch Chiefs, and then you could set currentpage for each pivot field.

    If not, please provide details information and share your excel file so we could try to reproduce your issue. You could share file with One Drive and put link here.

    Best Regards,

    Terry

    Wednesday, August 30, 2017 5:25 AM
  • Hello Terry,

    The cmbsearch is a combo box that I have on a userform that filters data by staff name.  What I am trying to do is filter pivot tables on the dashboard. I have the code I need to filter all pivot tables based on Pivot Table 5.  However I want the pivot tables to sort based on the staff selected from the cmbsearch combox.   Does this clarify?

    Thanks,

    Keri

    Wednesday, August 30, 2017 3:39 PM
  • Hi Lavenderchan,

    I think what you want has been done by combobox1, you fill items to combobox1 and use combobox1 change event to filter in Pivot table 5. I'm wondering what's the difference between combobox1 and cmbsearch.

    I suggest you share some screenshots help us know your requirement and provide your excel file so we could try to reproduce your issue. Thanks for understanding.

    Best Regards,

    Terry

    Friday, September 1, 2017 9:46 AM