RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 801
- Office Version
- 365
- Platform
- Windows
Hello, I have a userform that has 16 selectable checkboxes for region names (A1, A2, A3 etc) and I would like the result of these checkboxes to be applied to an Advanced Filter, the criteria of which is built dynamically.
Here is what my advanced filter criteria looks like:
Brochure Region is what I am filtering, and no matter what this must always exclude 0's.
Let's say of the 16 Regions, 4 are unticked. And these are A1, B1, C1 and D1.
It would be helpful if I could do something like this
Unfortunately, I do not think this particular is possible.
The way I see it, there's two problems here. One is that I need to get a complete list of what remains checked or unchecked. I understand the best way to do this would be to run through the frame and look at every checkbox, such as in this helpful post by Haluk on this forum:
That then gives me a list of all excluded clients, which I can pass into some kind of peverse search array similar to this:
(this is from something else, but I could modify it to suit my needs, for example removing the quotes and commas which aren't necessary here)
However from experience this is quite slow. But it would show >0 if any excluded regions are contained in the Brochure Region cell, and then I would filter this new row to only show 0's which means their region doesn't exist.
There's got to be an alternative way for this to work, however. Any ideas? Thanks.
Here is what my advanced filter criteria looks like:
| Brochure_Region | OptIn_NH_LandMail | Last_Booked | Last_Booked | FamilyFun | FamilyFun | UK | UK | Sports | Sports | Events | Events | EU | EU | Showtime | Showtime | OptIn_ThirdParty | OptIn_DirectEmail | OptIn_MktgEmail |
| <>0 | <>0 | >=0 | <=1000000 | >=1 | <=10000 | = | FamilyFun | |||||||||||
| <>0 | <>0 | >=0 | <=1000000 | >=1 | <=10000 | FamilyFun |
Brochure Region is what I am filtering, and no matter what this must always exclude 0's.
Let's say of the 16 Regions, 4 are unticked. And these are A1, B1, C1 and D1.
It would be helpful if I could do something like this
| Brochure_Region |
| <>0, <>A1, <>B1, <>C1, <>D1 |
| <>0, <>A1, <>B1, <>C1, <>D1 |
Unfortunately, I do not think this particular is possible.
The way I see it, there's two problems here. One is that I need to get a complete list of what remains checked or unchecked. I understand the best way to do this would be to run through the frame and look at every checkbox, such as in this helpful post by Haluk on this forum:
VBA Code:
Dim Ctrl As Control
For Each Ctrl In Me.Frame1.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
If Ctrl.Value = False Then
MyMsg = MyMsg & vbCrLf & Ctrl.Name & " is not selected"
End If
End If
Next
If MyMsg = Empty Then
MsgBox "All of them are selected"
Else
MsgBox MyMsg
End IfThat then gives me a list of all excluded clients, which I can pass into some kind of peverse search array similar to this:
Excel Formula:
Range("somewhere").FormulaArray = "=COUNT(SEARCH({"", " & Excluded1 & ","","", " & Excluded2 & ","","", " & Excluded3 & ","","", " & Excluded4 & ","","", " & Excluded5 & ","","", " & Excluded6 & ","","", " & Excluded7 & ",""},"", ""&RC[-14]&"",""))"(this is from something else, but I could modify it to suit my needs, for example removing the quotes and commas which aren't necessary here)
However from experience this is quite slow. But it would show >0 if any excluded regions are contained in the Brochure Region cell, and then I would filter this new row to only show 0's which means their region doesn't exist.
There's got to be an alternative way for this to work, however. Any ideas? Thanks.
