create search box/button to filter pivot table

77bn4fn4i

New Member
Joined
Jul 13, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
My data:
1626237330033.png

I currently manually filter on column labels to find the combos i am interested in. In the below case I clicked filter column labels, typed in A001 and then clicked enter, it then showed me all combos including A001.
My question is, is there any way to create a more user friendly search box/button, where the user searches what they want and it will update the pivot table, instead of them having to manually filter the pivot table.

Thanks
1626237436158.png
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
If you create a slicer for the pivot table on "Combo" you can use the code below in the Worksheet_Change event in the VBE to automatically filter the pivot table when entering your criteria into a specified cell. (In this case I used H7 but you can change this to suit your needs, you will just have to update the code where indicated.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim si As SlicerItem
    Dim sis As SlicerItems
    Dim f As Long
   
    Application.EnableEvents = False
   
    If Target.Address = "$H$7" Then   '<-----ADJUST CELL ADDRESS TO SUIT YOUR NEEDS
   
        srchStr = Target.Value
       
        Set sis = ActiveWorkbook.SlicerCaches("Slicer_Combo").SlicerItems
       
        For Each si In sis
            f = InStr(1, si.Name, srchStr)
            If f > 0 Then
                sis(si.Name).Selected = True
            Else
                sis(si.Name).Selected = False
            End If
        Next si
    End If
   
    Application.EnableEvents = True
End Sub

Book1
ABCDEFGHIJKLM
1IDDateCombo
226561/1/2021A001_B002
328461/2/2021A001
425601/3/2021B002_C001
523801/4/2021A001_C002
623251/5/2021C003_A002Enter Search Criteria
726141/6/2021A002
825481/7/2021A001_C004
922731/8/2021C002_B001_A002
10
11
12
13Count of ComboColumn Labels
14Row LabelsA001A001_B002A001_C002A001_C004A002B002_C001C002_B001_A002C003_A002Grand Total
151/1/202111
161/2/202111
171/3/202111
181/4/202111
191/5/202111
201/6/202111
211/7/202111
221/8/202111
23Grand Total111111118
Sheet1


Picture showing Slicer since it doesn't appear in the XL2BB mini sheet
1628799943761.png
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top