none
Multiple choice boxes that filters pivot table VBA RRS feed

  • Question

  • Hello,

    Hope you are doing great.

    I´m working on a project in Excel in which I need to build a multiple choice combo-box or list box for some categories that are populated from a data base. Also, this multiple choice box (categories) must populate the information of a pivot table through a VBA code.

    Can anyone help me with this?

    Thanks in advance.

    Regards

    Friday, May 22, 2015 10:06 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Monday, May 25, 2015 2:40 AM
  • Hi kabc04,

    What's kind of combo box are you using? As far as I know, there is two kinds of combo box in Excel. First is content control and the second is activeX control. You can get more detail about them from link below:
    Add a list box or combo box to a worksheet

    >>Also, this multiple choice box (categories) must populate the information of a pivot table through a VBA code.<<

    What kind of information did you want? We can use Pivot Table interop with the pivot table in workshees. And here is an example adds the page field names to a list on a new worksheet.

    Set nwSheet = Worksheets.Add 
    nwSheet.Activate 
    Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
    rw = 0 
    For Each pvtField In pvtTable.PageFields 
     rw = rw + 1 
     nwSheet.Cells(rw, 1).Value = pvtField.Name 
    Next pvtField 

    To filter the PivotTable, we can use PivotFields and here is an example for your reference:

     ActiveSheet.PivotTables("PivotTable1").PivotFields("PageField1").CurrentPage = _
            "(All)"
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("PageField1")
            .PivotItems("Item1").Visible = False
            .PivotItems("Item2").Visible = False
        End With
    Regards & Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, May 26, 2015 5:34 AM
    Moderator
  • Hi kabc04,

    What's kind of combo box are you using? As far as I know, there is two kinds of combo box in Excel. First is content control and the second is activeX control. You can get more detail about them from link below:
    Add a list box or combo box to a worksheet

    >>Also, this multiple choice box (categories) must populate the information of a pivot table through a VBA code.<<

    What kind of information did you want? We can use Pivot Table interop with the pivot table in workshees. And here is an example adds the page field names to a list on a new worksheet.

    Set nwSheet = Worksheets.Add 
    nwSheet.Activate 
    Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
    rw = 0 
    For Each pvtField In pvtTable.PageFields 
     rw = rw + 1 
     nwSheet.Cells(rw, 1).Value = pvtField.Name 
    Next pvtField 

    To filter the PivotTable, we can use PivotFields and here is an example for your reference:

     ActiveSheet.PivotTables("PivotTable1").PivotFields("PageField1").CurrentPage = _
            "(All)"
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("PageField1")
            .PivotItems("Item1").Visible = False
            .PivotItems("Item2").Visible = False
        End With
    Regards & Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thanks for your answer!

    I´m using a similar code but I cannot set ".PivotItems" because the pivot table is going to change according to other previous criteria in the Report Filter space (Ex: Year, Month....etc) that are populated with other combo boxes. (I´m working with an userform)

    Item A and Item B are also part of the Report Filter, but they are propulated from a column in the raw data of the pivot table.

    Code used in each Item combo_box:

    Private Sub combo_item1_Change()
    
     item1= combo_item1.Value
            
         
                       
        With Sheets("Pivot1").PivotTables("Table1").PivotFields("Item")
          .EnableMultiplePageItems = True
          .CurrentPage = item1
              
           End With
    
    
    End Sub

    But I don´t know how to make the pivot table recognize the 2 choices in the filter.



    • Edited by kabcr04 Tuesday, May 26, 2015 6:08 PM
    Tuesday, May 26, 2015 6:05 PM
  • Hi Kabcr04,

    >>I´m using a similar code but I cannot set ".PivotItems" because the pivot table is going to change according to other previous criteria in the Report Filter space (Ex: Year, Month....etc) that are populated with other combo boxes. (I´m working with an userform) <<

    Base on my understanding, it doesn't matter whther the pivot item was changed. We can declare a variable and set this variable based on the value we wanted and filter the pivot. Also here is a demo for your refernce:

    ActiveSheet.PivotTables("PivotTable1").PivotFields("PageField1").CurrentPage = _
            "(All)"
            
            Dim a(1) As String
            a(0) = "Item1"
            a(1) = "Item2"
            For Each filterStr In a
                   With ActiveSheet.PivotTables("PivotTable1").PivotFields("PageField1")
                        .PivotItems(filterStr).Visible = True
         
                End With
            Next filterStr
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 27, 2015 9:44 AM
    Moderator
  • Thanks, I tried your suggestion but the pivot table still is getting just one choice. :(
    Thursday, May 28, 2015 10:12 PM
  • Hi kabcr04,

    Since the code works well for me , would you mind sharing a sample workbook to help us to narrow down this issue?

    You can upload it via OneDrive and please remove the sensitive information before uploading.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 1, 2015 1:31 AM
    Moderator