Solved

Checkbox to filter accordingly

Posted on 2012-03-29
10
400 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 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

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 …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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 in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

829 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