none
use text box to auto-filter pivot field RRS feed

  • Question

  • Dears,

    i ask if can i use text box to auto-filter pivot field, i have this code:

    Private Sub TextBox1_Change()
                Range("A2").AutoFilter 1, "*" & [A1] & "*"
    End Sub

    to auto-filter a data filled in table, but can't use it with pivot.

    is there any other technique to use textbox to auto-filter with pivot  

    Gratefully,

    Monday, August 18, 2014 7:34 PM

Answers

  • Hi Ahmed,

    Wouter is correct. To filter a field that is in a pivot table, you need to loop all value in this field and set its visible property.

    Please try:

    Private Sub TextBox1_Change()
        Dim pi As PivotItem, pf As PivotField
        Set pf = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable2").PivotFields("Name")
        For Each pi In pf.PivotItems
             If InStr(1, pi.Value, TextBox1.Value, vbTextCompare) > 0 Then
                 pi.Visible = True
             Else
                 pi.Visible = False
             End If
        Next
    End Sub

    I'm not sure which value you need to add into the filter and which event you need to call, I use Textbox_change event and the value of this Textbox instead.

    Here is the sample workbook:

    https://onedrive.live.com/redir?resid=AD77AE76D657E280!171&authkey=!AGg-ER7tHFEXD7o&ithint=file%2cxlsm

    Regards,

    George


    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, August 20, 2014 3:45 AM
    Moderator

All replies

  • Hi Ahmed,

    Please try:

    Private Sub TextBox1_Change()
        ActiveSheet.Range("B1").AutoFilter Field:=1, Criteria1:="=*" & TextBox1.Text & "*"
    End Sub

    I don't know what "[A1]" is so I use the value of the textbox.

    It works fine for me in Excel 2013.

    Here is the sample for you to download and test:

    https://onedrive.live.com/redir?resid=AD77AE76D657E280!170&authkey=!AA0MkZmfX6_VYLg&ithint=file%2cxlsm

    Regards,

    George


    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, August 19, 2014 8:27 AM
    Moderator
  • Hi George,

    there's not any problem in applying it with a simple table, but when use it with a pivot it debugged

    it's a sample for my auto-filter sheet,   

    http://1drv.ms/YtwmH6

    Thanks a lot,

    • Proposed as answer by Wouter Defour Tuesday, August 19, 2014 1:38 PM
    • Unproposed as answer by Wouter Defour Tuesday, August 19, 2014 1:38 PM
    Tuesday, August 19, 2014 11:19 AM
  • If you want to use wildcards you might use something like this:

    Private Sub changePivotField(strInput As String)


        Dim pi As PivotItem, pf As PivotField
       
        Set pf = ThisWorkbook.Worksheets("yoursheetname").PivotTables("yourpivotnam").PivotFields("yourfield")
       
        For Each pi In pf.PivotItems
            If InStr(1, pi.Value, strInput, vbTextCompare) > 0 Then
                pi.Visible = True
            Else
                pi.Visible = False
            End If
        Next

    End Sub

    Tuesday, August 19, 2014 1:42 PM
  • Hi Ahmed,

    Wouter is correct. To filter a field that is in a pivot table, you need to loop all value in this field and set its visible property.

    Please try:

    Private Sub TextBox1_Change()
        Dim pi As PivotItem, pf As PivotField
        Set pf = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable2").PivotFields("Name")
        For Each pi In pf.PivotItems
             If InStr(1, pi.Value, TextBox1.Value, vbTextCompare) > 0 Then
                 pi.Visible = True
             Else
                 pi.Visible = False
             End If
        Next
    End Sub

    I'm not sure which value you need to add into the filter and which event you need to call, I use Textbox_change event and the value of this Textbox instead.

    Here is the sample workbook:

    https://onedrive.live.com/redir?resid=AD77AE76D657E280!171&authkey=!AGg-ER7tHFEXD7o&ithint=file%2cxlsm

    Regards,

    George


    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, August 20, 2014 3:45 AM
    Moderator
  • Hello George,

    it's really what i looking for 

    Thanks a lot, 

    Wednesday, August 20, 2014 8:16 AM