Check boxes sporadic filtering

[Rudi]

Hi,

I can't seem to identify the sporadic results of the check boxes in this file. It works sometimes and not other times.
Check one person and the filter is fine on the "Working" sheet
Check a second person and they filter fine
Remove the checks..and the whole list is back
Check two people and three appear in the list
Sometimes Kate is there and she is not checked
(Note: the above is just a description of the issue...it is sporadic so it might not happen as describes above. Just toggle boxes and see results)

Please assist with locating inconsistency.
File attached.
TX

Note: This is a issue I'm helping resolve on the Excel Forum site.

[HansV]

.End(xlUp) doesn't work correctly if there are hidden rows, so you have to unhide all rows before using .End(xlUp):

Code: Select all

Private Sub Worksheet_Calculate()
    Dim rC As Range, rData As Range, rF As Range, sCurr As String, lL As Long
    Worksheets("Working").Cells.EntireRow.Hidden = False
    Set rData = Worksheets("Working").Range("B3:B" & Worksheets("Working") _
        .Cells(Rows.Count, 1).End(xlUp).Row + 1)
    If Application.CountIf(Worksheets("Control").Range("A5:A11"), 1) = 0 Then
        Exit Sub
    End If
    On Error Resume Next
    For Each rC In Worksheets("Control").Range("A5:A11").Cells
        If rC.Value <> 1 Then
            Set rF = rData.Find(What:=rC.Offset(0, 3), LookIn:=xlValues)
            If Not rF Is Nothing Then
                rF.Resize(2).EntireRow.Hidden = True
            End If
        End If
    Next rC
    Application.ScreenUpdating = True
End Sub

I simplified the code a bit - it isn't necessary to unhide all rows twice if no check boxes are ticked.

[Rudi]

My hat off to you again Hans.
I must admit that I am learning a lot on this little venture and will only ask for assistance when I truly need it...
Cheers

[Rudi]

Hi,

Taking this one step further....
Is it possible to include the pivot table into the filter?

IOW: If I tick R1 in the userform, it filters *to remove* R1 from the list as well as from the pivot.
I have code in the filter button at the bottom...but it is not working.

Any ideas.
TX

[HansV]

You can't add a pivot filter of type xlCaptionDoesNotEqual with an array of values, as far as I know. The error was suppressed because of the On Error Resume Next.

You have to hide the individual pivot items:

Code: Select all

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Fund")
        .ClearAllFilters
        For Each cChk In Me.grpFilter.Controls
            If TypeName(cChk) = "CheckBox" Then
                If cChk.Value = True Then
                    .PivotItems(cChk.Tag).Visible = False
                End If
            End If
        Next cChk
    End With

BTW you used .PivotFields("Fund") both in the With ... line and within the With ... End With block. That was too much, but it wasn't the cause of the problem.

[Rudi]

That is doing the trick.
Much obliged.

I overlooked the With issue due to frustration... LOL... I was trying most everything to get the arrVals to work!!
TX for that knowledge transfer.