CODEWITHSUNDEEP
excelvbacheckboxuserformautofilter
Simon
Simon

Reputation: 3

VBA checkboxes to define autofilter criteria

I have reviewed numerous posts, but don't seem to be able to find an answer. Basically I have a table in excel with autofilter applied (I created the table by pressing ctr+L), and I want checkboxes in a userform to define which of the checkboxes in the autofilter are checked. When recording the code, I get:

Sub Testing1()
    ActiveSheet.ListObjects("Table96").Range.AutoFilter Field:=1, Criteria1:= _
        Array("company1", "company2", "company3"), Operator:=xlFilterValues
'then selecting one additional company
    ActiveSheet.ListObjects("Table96").Range.AutoFilter Field:=1, Criteria1:= _
        Array("company1", "company2", "company3", "company 5"), Operator:=xlFilterValues
End Sub

What I want to do is to be able to add/remove companies from that array using the checkboxes. If excel has the option, I want each checkbox that is associated with each company to add that company to the criteria array. Failing that, I want to be able to capture the existing criteria on that column, and apply that in conjunction with the new criteria.

Another potential solution would be to run a loop over all the checkboxes when the form is closed and somehow use the result of which checkboxes are selected to define the range.

Any help is greatly appreciated!

I am using Excel 2010

Upvotes: 0

Views: 3899

Answers (1)

Rory
Rory

Reputation: 34075

Sample code for a multiselect listbox:

Dim n                           As Long
Dim counter                     As Long
Dim asSelected()                As String
Dim lo                          As ListObject

Set lo = ActiveSheet.ListObjects("Table96")
With Me.ListBox1

    For n = 1 To .ListCount
        If .Selected(n - 1) Then
            ReDim Preserve asSelected(counter)
            asSelected(counter) = .List(n - 1)
            counter = counter + 1
        End If
    Next n
    If counter > 0 Then lo.Range.AutoFilter Field:=1, Criteria1:=asSelected, Operator:=xlFilterValues
End With

Upvotes: 1

Related Questions