VBA to use Checkbox Selection for Multiple cases

Question

I have designed an userform with 7checkboxes. Each Checkbox has an call function, which is called as autofilter. This function autofilter, helps to filter the field 4 in my sheet and Displays the result.

Here, I have 3 cases.

Case1. when Checkbox1 is selected, Display the result of autofilter1.

case2: when checkboxes1 and 2 are selected, Display the result of autofilter1 and 2.

Note: it is not necessary that user can select just checkboxes 1 and 2, it can be checkbox 2 and 3 or 1 and 3 as well or sometime all 3 selection.

Case3: when nothing is selected, clear the filter.

I am successful in generating the case1 Situation, How should I proceed, in order to achieve case2 and case3.

Below is the autofilter function, which is assigned to checkbox1.

Sub autofilter1()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Result")
wslr = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set myfilt = ws.Range("A1:D" & wslr)
myfilt.autofilter Field:=4, Criteria1:= _
"USA"
End Sub

autofilter function assigned to checkbox2.

Sub autofilter2()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Result")
wslr = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set myfilt = ws.Range("A1:D" & wslr)
myfilt.autofilter Field:=4, Criteria1:= _
"Germany"
End Sub

autofilter function assigned to Checkbox 3.

Sub autofilter3()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Result_APQP")
wslr = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set myfilt = ws.Range("A1:D" & wslr)
myfilt.autofilter Field:=4, Criteria1:= _
"France"
End Sub

In the Command Button, "Go", I have the following code,

Private Sub CommandButton4_Click()
If CheckBox1.Value = True Then
Call autofilter1
End If
If CheckBox2.Value = True Then
Call autofilter2
End If
If CheckBox3.Value = True Then
Call autofiletr3
End If
End Sub

I have attached the Image also, for reference. In the Image, i have just taken the example for 3 Checkbox.

next time sumarize a little plz, just ask the basis problem. Make a sub for each case, and ´select case true : case checkbox1.value : sub1: case check2: sub2 .....end select´ . Sorry i am on phone app, can't format the code better. — Patrick Lepelletier

Rik Sportel Rik Sportel · Accepted Answer · 2017-07-04T08:52:11

This is code for a UserForm module.

I made a UserForm with 3 checkboxes.

Each event handler just fires the DoFilter() method, which checks the checkboxes and for the ones that are true, builds string array based on their Caption (as in your example pictures) and passes that Array to the AutoFilter. If the Array is empty, the autofilter gets disabled.

Edit: In the Visual Basic Editor (ALT+F11), in the Project window right-click. In the menu select Insert -> UserForm. On this UserForm, add a number of CheckBoxes. For each checkbox, change the Caption property in the Properties window to a value that you want to display in the filtered range, e.g. CheckBox1.Caption would be "USA" as per the question. Next, Right-click on the userform module and select View Code. In the code window, paste everything below:

Private Sub DoFilter()

Dim ws As Worksheet
Dim strCriteria() As String
Dim arrIdx As Integer

Dim cBox As Control

arrIdx = 0
For Each cBox In Me.Controls
    If TypeName(cBox) = "CheckBox" Then
        If cBox.Value = True Then
            ReDim Preserve strCriteria(0 To arrIdx)
            strCriteria(arrIdx) = cBox.Caption
            arrIdx = arrIdx + 1
        End If
    End If
Next cBox

Set ws = ThisWorkbook.Sheets("Sheet2")
If arrIdx = 0 Then
    ws.UsedRange.AutoFilter
Else
    ws.Range("A:D").AutoFilter Field:=4, Criteria1:=Array(strCriteria), Operator:=xlFilterValues
End If

End Sub

Private Sub CheckBox1_Change() 'Repeat for each CheckBox on your form.
    DoFilter
End Sub
Private Sub CheckBox2_Change() 'Repeat for each CheckBox on your form.
    DoFilter
End Sub
Private Sub CheckBox3_Change() 'Repeat for each CheckBox on your form.
    DoFilter
End Sub

**Additional Edit:**N.b. in this case the filter will trigger as soon as you change the checkbox. If you want to apply the filter on the click of a commandbutton, you can also put the following code in the UserForm module, instead of the Private Sub CheckBox#_Change()

Private Sub CommandButton4_Click()
    DoFilter
End Sub

You might want to use a Control Array in order not to have each CheckBox_Change event handler seperately listed.

VBA to use Checkbox Selection for Multiple cases

1 Answers