Hi Ocean -
Try this version. Copy/Paste into your Sheet1 object in the VB Editor.
This version does not require a pre-existing autofilter; if an autofilter
doesn't exist, it will start one.
-------
Private Sub ToggleButton1_Click()
'Filtermode=true if an autofilter is present and there are hidden rows
in its list.
'Reset the filter and update toggle button text.
If ActiveSheet.FilterMode = True Then
[oceanHomeCell].AutoFilter
[oceanHomeCell].AutoFilter
ActiveSheet.ToggleButton1.Caption = "Push To Filter"
Exit Sub
End If
'Autofilter is present, but all rows are visible OR the list has no
autofilter at all.
'Set the autofilter (with criteria) and update toggle button text.
[oceanHomeCell].AutoFilter Field:=4, Criteria1:="=Yes", Operator:=xlOr,
Criteria2:="=."
ActiveSheet.ToggleButton1.Caption = "Push To Remove Filter"
End Sub
-----
Jay
"Jay" wrote:
> Hi Ocean -
>
> In the second line, change the word "Autofiltermode" to "Filtermode".
>
> --
> Jay
>
>
> "ocean" wrote:
>
> > Thanks a ton for the help Jay. I placed an embeded toggle button on my sheet
> > and named the cell as you advised. I placed the code in Sheet1 as follows
> > but it will will not do anything. No errors either
> >
> > Private Sub ToggleButton1_Click()
> > If ActiveSheet.AutoFilterMode = True Then
> > [oceanHomeCell].AutoFilter
> > [oceanHomeCell].AutoFilter
> > ActiveSheet.ToggleButton1 = False
> > ActiveSheet.ToggleButton1.Caption = "Push To Filter"
> > Exit Sub
> > End If
> >
> > If ActiveSheet.AutoFilterMode = True Then
> > [oceanHomeCell].AutoFilter Field:=4, Criteria1:="=Yes", Operator:=xlOr, _
> > Criteria2:="=."
> > ActiveSheet.ToggleButton1 = True
> > ActiveSheet.ToggleButton1.Caption = "Push To Remove Filter"
> > End If
> >
> > End Sub
> >
> >
> > "Jay" wrote:
> >
> > > Hi Ocean -
> > >
> > > Try the following, but first name the upper left-most cell of your list
> > > "oceanHomeCell" (Insert, Name, Define). That just gives the code an
> > > unambiguous reference point. Also, use the Control Toolbox to add a toggle
> > > button to the worksheet. Good luck!
> > >
> > > Private Sub ToggleButton1_Click()
> > >
> > > If ActiveSheet.FilterMode = True Then
> > > [oceanHomeCell].AutoFilter
> > > [oceanHomeCell].AutoFilter
> > > ActiveSheet.ToggleButton1 = False
> > > ActiveSheet.ToggleButton1.Caption = "Push To Filter"
> > > Exit Sub
> > > End If
> > >
> > > If ActiveSheet.AutoFilterMode = True Then
> > > [oceanHomeCell].AutoFilter Field:=1, Criteria1:=">5", Operator:=xlOr, _
> > > Criteria2:="<3" <<<<==========Change criteria to suit
> > > ActiveSheet.ToggleButton1 = True
> > > ActiveSheet.ToggleButton1.Caption = "Push To Remove Filter"
> > > End If
> > >
> > > End Sub
> > > --
> > > Jay
> > >
> > >
> > > "ocean" wrote:
> > >
> > > > Thanks Jay. That worked great. I have that recorded macro tied to a button
> > > > on the sheet now. Is there a way to toggle this back to unfiltered by
> > > > clicking the button again?
> > > >
> > > > "Jay" wrote:
> > > >
> > > > > Hi Ocean-
> > > > >
> > > > > Start the macro recorder and then build your autofilter. Stop the macro
> > > > > recorder and view the result. With a tiny bit of tinkering, you'll have your
> > > > > code.
> > > > > --
> > > > > Jay
> > > > >
> > > > >
> > > > > "ocean" wrote:
> > > > >
> > > > > > Hello all! I need help creating a command button that applies a filter. I
> > > > > > don't want to use the drop down to select the filter everytime. I have to
> > > > > > use a customer filter and dont want to have the user to select the options
> > > > > > everytime. I just need a filter that displays the rows that contain the word
> > > > > > Yes or . in the D column. Any help would be greatly appreciated. Thanks in
> > > > > > advance
|
Similar Threads
