Autofilter Data Using Pul-Down List or Checkboxes

G

gidge223

New member
Joined
Oct 5, 2017
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2016
Hello,


I have a document that I need to use a pull-down list or checkboxes to automatically hide rows (filter). The goal is to have the operator perform fewer keyboard actions, other than manually having to use an auto filter on the column header and uncheck these options manually.


Explanation:
The Document consists of many rows of data, each column having a heading, one of them being "CategoryLevel" already assigned. See attached example document with limited rows of data for reference. The function I want to add is to have a pull-down list or checkboxes labeled as "Cat 1", "Cat 2", "Cat 3", and "Cat 4" at the top left of the document. Here the user can select an option from the pull-down list or check the appropriate checkbox depending what category they have. So let's say the user checks the "Cat 1" checkbox, all rows below that have a Cat 1 level assigned to that row, I would like all of those rows to remain visible while all other rows (not category 1) to be hidden.

For reference, I have tested code courtesy of Hans Vogelaar from this forum, but with no success. Maybe this code, slightly modified, can be a key to my answer?

Anyway this can be done is appreciated.


If using a drop-down box instead of checkboxes is easier, the method of accomplishing this task would be very helpful.
 

Attachments

  • Book10.xlsx
    10 KB · Views: 146
Gidge,

Why reinvent the wheel? This feature is included in the AutoFilter selection on the Data Tab.
AutoFilter.PNG

If you uncheck the Select All item then check the Cat3 you get:
Filtered.PNG

HTH :cool:
 
According to the OP's first pargraph, I don't think they want to reinvent the wheel, just automate it a little.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim FiltRng As Range
    
'limit to single cell
If Target.Count <> 1 Or Target.Address <> "$G$4" Then Exit Sub

'set the range to filter
Set FiltRng = Range("C11", Range("G" & Rows.Count).End(xlUp))

With FiltRng
    If Target.Value = "" Then
        .AutoFilter
    Else
        .AutoFilter Field:=5, Criteria1:=Target.Value
    End If
End With

End Sub
 
retired007geek,

Thank you for the suggestion, but as NoS said, I was looking for a way to add some automation.


NoS,

Thank you so much for the speedy reply and for the functioning code! I didn't realize the field number as to be adjusted.

Your code works for a drop-down list. If you have the time/knowledge can the same functionality be duplicated using Form Control Checkboxes (See attached "book12" for reference) or a Form Control List Box that the "Selection Type" is set to "Multi" for multiple selections (See attached "book11" for reference)? The reason I ask is, if the user wants to filter multiple items at once, instead of only "Cat 1" or "Cat2", etc at a single time.

Again I appreciate your help!
 

Attachments

  • Capture4.PNG
    Capture4.PNG
    48.6 KB · Views: 106
  • Book11.xlsm
    17.5 KB · Views: 124
  • Book12.xlsm
    20.9 KB · Views: 175
Since you seem to be using Excel 2016 (?) (I'm only on Excel 2010), I believe you can (since Excel 2013) use slicers on a Table. Add a slicer for the Category level and you should be able to select single/multiple categories using the usual combinations of Shift and left-click, Ctrl + left-click on it, to select what you want to see. You may even be able to change the display of the slicer to include checkboxes.
 
Seems like reinventing the wheel to me too but it matters little.

For the form listbox control's file, you would be better off getting items for your listbox list from column G unique values rather than a static list. If you like, I can show you how to do that which is what Autofilter does too.

Obviously, you would Assign this macro in a Module to your form listbox control.
Code: 
Sub ListBox16_Change()  
  Dim i As Integer, j As Integer
  Dim r As Range, a
  
  Set r = Range("C9:G" & Cells(Rows.Count, "C").End(xlUp).Row)
  
  With Sheet1.Shapes("List Box 16").OLEFormat.Object
    'Form Listbox's ListIndex is base 1, not base 0 like ActiveX Listbox
    For i = 1 To .ListCount
      If .Selected(i) = True Then j = j + 1 'Count number of selected items.
    Next i
    If j = 0 Then
      Sheet1.AutoFilter.ShowAllData
      Exit Sub
    End If


    ReDim a(1 To j)
    j = 0
    For i = 1 To .ListCount
      If .Selected(i) = True Then
        j = j + 1
        a(j) = .List(i)
      End If
    Next i
  End With
  
  r.AutoFilter 5, a, xlFilterValues
End Sub
 
Last edited:
Here is my form checkbox solution. It is similar to the form listbox solution.

Assign this Module macro to each of the 4 check box controls.
Code: 
Sub CheckBox_Click()  
  Dim i As Integer, j As Integer
  Dim r As Range, a(1 To 4), b
  
  Set r = Range("C9:G" & Cells(Rows.Count, "C").End(xlUp).Row)
  
  For i = 1 To UBound(a)
    a(i) = Sheet1.Shapes("Check Box " & 4 + i).OLEFormat.Object.Value
    If a(i) = 1 Then j = j + 1 '1=True (checked), -4146=False (unchecked)
  Next i
  If j = 0 Then
    Sheet1.AutoFilter.ShowAllData
    Exit Sub
  End If
  
  ReDim b(1 To j)
  j = 0
  For i = 1 To UBound(a)
    If a(i) = 1 Then
      j = j + 1
      b(j) = Sheet1.Shapes("Check Box " & 4 + i).OLEFormat.Object.Caption
    End If
  Next i
  
  r.AutoFilter 5, b, xlFilterValues
End Sub
 
You must log in or register to reply here.
Back
Top