Solved

Checkbox to filter accordingly

Posted on 2012-03-29
10
401 Views
Last Modified: 2012-08-06
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
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 

Author Comment

by:Rayne
ID: 37785107
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37785857
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
 

Author Comment

by:Rayne
ID: 37788097
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!

 
LVL 42

Expert Comment

by:dlmille
ID: 37788875
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37789106
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
 

Author Comment

by:Rayne
ID: 37789283
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37789290
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
 

Author Comment

by:Rayne
ID: 37789349
Hi Dave,

Got it. Now I understand. Thank you so much for your kind help. Greatly appreciated.
0
 

Author Closing Comment

by:Rayne
ID: 37789352
Awesome help again...
0
 

Author Comment

by:Rayne
ID: 38264783
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question