Solved

Checkbox to filter accordingly

Posted on 2012-03-29
10
397 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
This article will show you how to use shortcut menus in the Access run-time environment.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

708 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

17 Experts available now in Live!

Get 1:1 Help Now