Results 1 to 7 of 7

Thread: Autofilter Data Using Pul-Down List or Checkboxes

  1. #1
    Neophyte gidge223's Avatar
    Join Date
    Oct 2017
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Autofilter Data Using Pul-Down List or Checkboxes



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Acolyte retired007geek's Avatar
    Join Date
    Jun 2017
    Location
    South Carolina, USA
    Posts
    86
    Articles
    0
    Gidge,

    Why reinvent the wheel? This feature is included in the AutoFilter selection on the Data Tab.
    Click image for larger version. 

Name:	AutoFilter.PNG 
Views:	97 
Size:	40.0 KB 
ID:	7485

    If you uncheck the Select All item then check the Cat3 you get:
    Click image for larger version. 

Name:	Filtered.PNG 
Views:	36 
Size:	7.7 KB 
ID:	7486

    HTH

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    768
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

  4. #4
    Neophyte gidge223's Avatar
    Join Date
    Oct 2017
    Posts
    3
    Articles
    0
    Excel Version
    2016
    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!
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture4.PNG 
Views:	53 
Size:	48.6 KB 
ID:	7487  
    Attached Files Attached Files

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    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.

  6. #6
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    130
    Articles
    0
    Excel Version
    365
    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 by Kenneth Hobson; 2017-10-06 at 05:48 PM.

  7. #7
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    130
    Articles
    0
    Excel Version
    365
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •