[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Checkbox to filter accordingly

In the [All] worksheet, by default, no check boxes will be selected.
As users check different boxes, the information gets filtered accordingly for any combination of check boxes. How do I go about a VBA code that will do it? I have attached the file here.

Thanks in advance

-R
nextbutton.xlsm
0
Rayne
Asked:
Rayne
  • 6
  • 4
1 Solution
 
RayneAuthor Commented:
Correction: For the checkboxes
There will actually be three [Sublist ] checkboxes: l1,l2,l3
There will actually be 3 [Division] checkboxes:  Div1,Div2,Div3
0
 
dlmilleCommented:
I'm not sure why we want to create apps like this as opposed to using the very powerful datafilter directly....

Anyway, I created you 3 sublist and 3 division checkboxes, then renamed them and wrote the code against it.  Please ask what questions you need to understand it so you can enhance as you develop.

Option Explicit
Const divisions = "DIV1,DIV2,DIV3"
Const sublist = "L1,L2,L3"
Sub subList1_Click()
    Call dofilter
End Sub
Sub subList2_Click()
    Call dofilter
End Sub
Sub subList3_click()
    Call dofilter
End Sub
Sub divList1_click()
    Call dofilter
End Sub
Sub divList2_click()
    Call dofilter
End Sub
Sub divList3_click()
    Call dofilter
End Sub
Sub CBInitialize(wks As Worksheet)
Dim CBox As CheckBox

    For Each CBox In wks.CheckBoxes
        If Left(UCase(CBox.Name), 2) = "CB" Then
            CBox.Value = False
        End If
    Next CBox
    
    If wks.AutoFilterMode Then wks.AutoFilterMode = False
    
End Sub
Sub dofilter()
Dim wkb As Workbook
Dim wks As Worksheet
Dim lastRow As Long
Dim rTable As Range
Dim lDivisions As Long
Dim lSubList As Long
Dim lfield As Long
Dim strTmp As String
Dim strFilter1 As String
Dim strFilter2 As String
Dim CBox As CheckBox

Application.ScreenUpdating = False

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("All")
    
    lastRow = wks.Range("B" & wks.Rows.Count).End(xlUp).Row
    
    Set rTable = wks.Range("B" & 9 & ":J" & lastRow)
    
    'get filter fields relative to left edge of table
    lDivisions = wks.Columns("H").Column - rTable.Cells(1, 1).Column + 1
    lSubList = wks.Columns("I").Column - rTable.Cells(1, 1).Column + 1
    
    For Each CBox In ActiveSheet.CheckBoxes
        If CBox.Value = 1 Then
            Select Case UCase(CBox.Name)
                Case "CBSUBLIST1":
                    strTmp = "L1"
                Case "CBSUBLIST2":
                    strTmp = "L2"
                Case "CBSUBLIST3":
                    strTmp = "L3"
                Case "CBDIVISION1":
                    strTmp = "Div1"
                Case "CBDIVISION2":
                    strTmp = "Div2"
                Case "CBDIVISION3":
                    strTmp = "Div3"
                Case Else:
            End Select
            
            If Left(UCase(CBox.Name), 3) = "CBS" Then
                If strFilter2 = vbNullString Then
                    strFilter2 = strTmp
                Else
                    strFilter2 = strFilter2 & "," & strTmp
                End If
            Else
                If strFilter1 = vbNullString Then
                    strFilter1 = strTmp
                Else
                    strFilter1 = strFilter1 & "," & strTmp
                End If
            End If
        End If
    Next CBox
    
    'now create autofilter
    If wks.AutoFilterMode Then wks.AutoFilterMode = False
    
    If strFilter1 <> vbNullString Then
        rTable.AutoFilter Field:=lDivisions, Criteria1:=Array(Split(strFilter1, ",")), Operator:=xlFilterValues
    End If
    
    If strFilter2 <> vbNullString Then
        rTable.AutoFilter Field:=lSubList, Criteria1:=Split(strFilter2, ","), Operator:=xlFilterValues
    End If
    
Application.ScreenUpdating = True

End Sub

Open in new window


There's a call to CBInitialize in the .Activate event of the ALL sheet.

See attached.

Dave
nextbutton-r1.xlsm
0
 
RayneAuthor Commented:
Hi Dave,

Thanks again for your  help

..i looked over the code and buttons….

If CBox.Value = 1 Then
            Select Case UCase(CBox.Name)
                Case "CBSUBLIST1":
                    strTmp = "L1"
                Case "CBSUBLIST2":
                    strTmp = "L2"
                Case "CBSUBLIST3":
                    strTmp = "L3"
                Case "CBDIVISION1":
                    strTmp = "Div1"
                Case "CBDIVISION2":
                    strTmp = "Div2"
                Case "CBDIVISION3":
                    strTmp = "Div3"
                Case Else:
            End Select
           
            If Left(UCase(CBox.Name), 3) = "CBS" Then
                If strFilter2 = vbNullString Then
                    strFilter2 = strTmp
                Else
                    strFilter2 = strFilter2 & "," & strTmp
                End If
            Else

 The three checkbox have these names of their function
Sub subList1_Click()
Sub subList2_Click()
Sub subList3_Click()

How in the code, CBSUBLIST1 is mentioned-
If that’s a checkbox name then it should be sublist1, sublist2 or sublist3 – isn’t it? Or I didn’t understand that piece of code.

The CBSublist and CBDivisions - if you can explain more on them -
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dlmilleCommented:
I named all the check boxes accordingly.

Click on them and you'll see there names in the name window to the left on the formula bar.
0
 
dlmilleCommented:
Ahhh- when you look at the checkbox the CAPTION can be pretty much any text you want, but the NAME of the checkboxes is a different name.  Originally it was probably Check Box 1, Check Box 2, etc., then I named them according to what they were, with no spaces, re: CBSubList1 to 3, and CBDivisions1 to 3.  If you create more, you can use their default name, or rename them following this convention as well.

Do you have any additional questions?

Dave
0
 
RayneAuthor Commented:
Thank you for your reply Dave..

One last question;
¬aobut this line, you are creating
lDivisions = wks.Columns("H").Column - rTable.Cells(1, 1).Column + 1

if you can just explain what’s happening. That’s it.
0
 
dlmilleCommented:
it indicates what column in the table (to be used with the data/autofilter to identify the criteria field) that relates to the divisions.  Hard coded it I think would be column 7.  The "H" can be changed to another column if you are adding a new criteria field or you move the divisions location.

Dave
0
 
RayneAuthor Commented:
Hi Dave,

Got it. Now I understand. Thank you so much for your kind help. Greatly appreciated.
0
 
RayneAuthor Commented:
Awesome help again...
0
 
RayneAuthor Commented:
Hello Dave,

All Hats off to you. I think this is the very first question I asked you months ago and you indicated that instead of using apps that use advanced filter, why not use the In-built autofilter as you say here....I should have listened to you then. With 35000 rows, I can see autofilter can be a better option that advanced filter, in temrs of the speed. the advanced filter can be slow at times, with that many rows.  

But thank you for your honest and true suggestions, whenever you gave me one  :)

Rayne
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now