Solved

Checkbox to filter accordingly

Posted on 2012-03-29
10
398 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
  • 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 41

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
 
LVL 41

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 41

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 41

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now