chk box to filter or hide rows based on value in a column

[deanop]

Dear Excel Power Users,

column b has an expression that results in a zero or 1, I wish to
filter out those rows in a range in which column b has a value of zero

Using forms control, I created a forms chk box with the intent to be
able to hide those rows with column b = 0, and then expand the rows
when box is unchecked.

the expression in column b looks at column A which has a date and
assigns a value of 1 for those dates within 5 days of today's date.
Expression in column
b=IF($A108<INT(NOW())-15,0,IF($A108>INT(NOW())+15,0,1))

well i could not get it to work that way, can u advise on how to get
one form chk box to filter out/hide those rows and expand those same
rows when unchecked? Thanks


but I added the below macros and assigned each to one form chk box.
So that now I chk the box assigned to hiderow_criteria and it does what
I want, but then I have to chk the other box to get to expand.

Sub hiderow_criteria()
'
Range("A16:B16").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
End Sub
'
Sub Unhiderow_criteria()
'
' Unhiderow_criteria Macro
' Macro recorded 1/4/2005 by dabbad
'
Selection.AutoFilter
End Sub

 

[Dave Peterson]

You can check the value of that checkbox:

Option Explicit
Sub ShowHide()
Dim CBX As CheckBox
With ActiveSheet
Set CBX = .CheckBoxes(Application.Caller)
If CBX.Value = xlOn Then
.Range("a16:b16").AutoFilter Field:=2, Criteria1:="1"
Else
If .AutoFilterMode Then
.ShowAllData
End If
End If
End With
End Sub

 

[deanop]

Thanks Dave for the feedback......I added the propsed code and assigned
to a new forms control chk box.

When the box is checked, the code adds filter drop downs but does not
proceed to filter out the rows.
When the box is unchecked, the code excecution is interrupted because
the .ShowAll Data method fails
Any thoughts on how to overcome.....

Thx, Deano

 

[Tom Ogilvy]

Option Explicit
Sub ShowHide()
Dim rng as Range
With Activesheet
set rng = .Range(.Range("A16"),.Range("A16").End(xldown))
End With
set rng = rng.Resize(,2)
Dim CBX As CheckBox
With ActiveSheet
Set CBX = .CheckBoxes(Application.Caller)
If CBX.Value = xlOn Then
rng.AutoFilter Field:=2, Criteria1:="1"
Else
If .AutoFilterMode Then
if .FilterMode then
.ShowAllData
End if
End If
End If
End With
End Sub

Should fix it.

 

[Dave Peterson]

Thanks for the correction, Tom.

Option Explicit
Sub ShowHide()
Dim rng as Range
With Activesheet
set rng = .Range(.Range("A16"),.Range("A16").End(xldown))
End With
set rng = rng.Resize(,2)
Dim CBX As CheckBox
With ActiveSheet
Set CBX = .CheckBoxes(Application.Caller)
If CBX.Value = xlOn Then
rng.AutoFilter Field:=2, Criteria1:="1"
Else
If .AutoFilterMode Then
if .FilterMode then
.ShowAllData
End if
End If
End If
End With
End Sub

Should fix it.

 

[deanop]

It worked like a champ...thanks...so it needed to know the end of the
autofilter range, but if u look at the code I created I did not have to
define an end for autofilter range, why did Dave's code failed to
execute.....Thx, deano

 

[Dave Peterson]

I figured that letting excel guess worked for you before, it would work for you
in code.

(And I bet it still would if you don't have any gaps in that range.)

But the real problem was my mistake in this area:

If .AutoFilterMode Then
.ShowAllData
End If

Tom corrected it to:

If .AutoFilterMode Then
if .FilterMode then
.ShowAllData
End if
End If

..autofiltermode checks to see if you have those visible arrows applied.
..filtermode checks to see if you're actually filtering any of the columns.

(I had a brain freeze and my little testing wasn't much of a test.)