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.
Check boxes sporadic filtering
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Check boxes sporadic filtering
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check boxes sporadic filtering
.End(xlUp) doesn't work correctly if there are hidden rows, so you have to unhide all rows before using .End(xlUp):
I simplified the code a bit - it isn't necessary to unhide all rows twice if no check boxes are ticked.
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
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Check boxes sporadic filtering
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
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Check boxes sporadic filtering
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
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
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78675
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check boxes sporadic filtering
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:
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.
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
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Check boxes sporadic filtering
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.
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.