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

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

Code for the checkbox:

Private Sub CheckBox1_Change()
Call testcode
End Sub



Code for Showing, hiding:

Sub testcode()

If UserForm1.CheckBox1.Value = True Then
Selection.AutoFilter Field:=1, Criteria1:="1"
Else:
Selection.AutoFilter Field:=1
End If

End Sub

- Pete

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 wrote:
>
> 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

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

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.

--
Regards,
Tom Ogilvy


"deanop" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Thanks for the correction, Tom.

Tom Ogilvy wrote:
>
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "deanop" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

--

Dave Peterson

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

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.)

deanop wrote:
>
> 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