Filter Excel cells using UserForm CheckBoxes

Hi all,

I am fairly new to VBA; I am trying to filter multiple selections in one column using a VBA Userform with several checkboxes.

I currently possess the code to filter fields in a column, using checkboxes located on the worksheet, but I need to be able to use it with a userform (to act as a "Control Panel" for a large workbook)

I have attached the sample code. Thanks in advance!

Josh

Awesome! That is perfect, thank you.


I have another question, regarding this.

Instead of filtering to show items to only show the selected fields. Is there a way to make it so checking the box "hides it"?

This works perfectly. I just wondered whether there was a way to make the filter a contains with filter, rather than an outright filter of the control tag. Usually putting asterisks around the Criterial sorts this out; but not sure how it would work here?

Thanks for the help

Agradeço muito meu amigo... seu trabalho me ajudou demais na minha planilha... desde ontem estava procurando e hoje encontrei. Que Deus lhe dê em dobro... forte abraço...

Outstanding!

I think I might just set the default value to true and have user uncheck items.

Hopefully that wouldn't be too counter intuitive.


I appreciate the help on a short notice; I think I will find myself collaborating here frequently for R&D. You saved so much time. If only I could have been less stubborn and posted this question a couple days ago, I could have saved even more time.

You're a rock star!

Thanks again,

Josh

I do see one minor glitch though. I am wondering if there is an easy solution.

I am wondering if there is a way to ingore criteria that is not listed as a Check Box.

For example:

Fields:
A
B
C
D

Available Check Boxes

A
B
D

As of now if I were to use the "Exclude UserForm" and selected checkbox D, the C field will also be excluded from the filter. (I believe because it isn't listed.)

Thanks!

Do you have an idea about the compile error?

I was able to do a fix, by adding a hidden tab with unused checkboxes containing the rest of the filters. That issue is resolved. After integrating this code with my real project, I have been getting another issue though.


I have changed

Private Sub cmdFilter_Click()
Dim cChk As Control, arrVals() As Variant, i As Integer
    Unload Me
    For Each cChk In Me.grpFilter.Controls
    i = i + 1
    If cChk.Value = False Then
        ReDim Preserve arrVals(1 To i)
        arrVals(i) = cChk.Tag
    End If
    Next cChk
    On Error Resume Next
    ActiveWorksheet.Range("A12").CurrentRegion.AutoFilter _
            Field:=2, _
            Criteria1:=Array(arrVals), _
            Operator:=xlFilterValues
End Sub

To

Private Sub cmdFilter_Click()
Dim cChk As Control, arrVals() As Variant, i As Integer
    Unload Me
    For Each cChk In Me.grpFilter.Controls
    i = i + 1
    If cChk.Value = False Then
        ReDim Preserve arrVals(1 To i)
        arrVals(i) = cChk.Tag
    End If
    Next cChk
    On Error Resume Next
    Worksheets("MyWorksheet").Range("K:K").CurrentRegion.AutoFilter _
            Field:=11, _            Criteria1:=Array(arrVals), _
            Operator:=xlFilterValues
End Sub

I have been getting an error

Compile Error: Method or data memer not found

with GrpFilter highlighted. Any suggestions?

Alright, everything is working on the live version, one slight (But I think it is last!) issue is that it keeps unselecting 0 in one of the collumns.

The data I use comes off an ODC connection from a SharePoint source. So blank cells within the collumn return back as 0s. For some reason the code does not like numerical 0.

Other than that, this is all fantastic.

I do not have a 0 checkbox, but I do have a 0 within the that collumn, because SharePoint translates "blanks" to 0s. For some reason though, the code is unselecting 0s without a checkbox calling it.

I have one more idea! (Then I think we can put this thread to rest) Is there a way to add multiple sort fields to ONE checkbox?

It streams 4 databases to one workbook using Excel's Office Data Connection (ODC). It is a good size project, constantly updating.

That is a great resource, I will read into it this morning.

Appreciate the link.

So at this point, I am wondering if it would be ideal to have all the checkboxes preselected which would make the Worksheet show all content.

Then the User can expand let’s say... by year

2011 2012 2013 2014 2015

So if you were to click 2014 to expand the checkboxes you can deselect column fields under that tree. (R1, R2 and R3)

The years can all be static, they do not have to be dynamic, just having the tree functionality.

(Sorry for the double post)

No problem, you have been more than enough help, I think the guide you have posted will work just fine. I know it's difficult to invision without the actual project. Pivots would not fulfil the end goal for this project unfortunatley, or I would be all over that.

Thank you again for all of your help.

I couldn't have done it without you.

Sorry, I don't think I communicated the issue well enough.

It works fine, but when I try to move the code to a different workbook, I get an error

Compile Error:

Method or Data Member not found

with .grpFilter highlighted.

I did a search for grpFilter, I can't find it.

I am an idiot...

Thanks RudiS