Command Button

[dickharlo]

I'm trying to create a command button to clear filters in my
spreadsheet. I don't know how to write the commands, but can recored
a macro and run it fine. I tried pasting that macro into the
button....and I had it working then messed it up. Now when I click on
the button, nothing happens. The code currently associated to the
button is below. I really don't want Criteria1 to = 'Reject', but the
one time the button worked if no filter was set and the button
clicked, an error was returned. The only way I could make it work was
to force a filter as part of the macro.

How do I make the clear filter work by clicking the button, and can it
be made so that if there isn't a filter set when the button is
clicked, it does nothing rather than return error?

Sub Click()
'
' Macro1 Macro
' Macro recorded 1/7/2009 by Ben DiCarlo
'
Rows("8:8").Select
Selection.AutoFilter Field:=2, Criteria1:="Reject"
Rows("8:8").Select
ActiveSheet.ShowAllData
Range("A7").Select

End Sub

Private Sub CommandButton1_Click()

End Sub

 

[Dave Peterson]

Try:

Private Sub CommandButton1_Click()
If me.FilterMode Then
me.ShowAllData
End If
End Sub

==========
In xl2003 menus, I can add a ShowAllButton to my favorite toolbar.

Tools|Customize|Commands tab|Data category
Drag the "Show All" icon to your favorite toolbar.

I like it since I can click on it in any worksheet in any workbook.

 

[dickharlo]

Try:

Private Sub CommandButton1_Click()
    If me.FilterMode Then
        me.ShowAllData
    End If
End Sub

==========
In xl2003 menus, I can add a ShowAllButton to my favorite toolbar.

Tools|Customize|Commands tab|Data category
Drag the "Show All" icon to your favorite toolbar.

I like it since I can click on it in any worksheet in any workbook.












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Worked perfect Dave....Thank you!