Clear Filter with a button

%3CLINGO-SUB%20id%3D%22lingo-sub-199920%22%20slang%3D%22en-US%22%3EClear%20Filter%20with%20a%20button%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-199920%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20only%20a%20%22medium%22%20excel%20guy%2C%20please%20be%20patient....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20a%20button%20to%20a%20spreadsheet%20that%20has%20columns%20that%20can%20be%20filtered.%26nbsp%3B%20I%20have%20assigned%20the%20%22clear%22%20function%2Fmacro%20to%20the%20button.%26nbsp%3B%20However%2C%20if%20no%20columns%20are%20filtered%20at%20the%20time%20that%20the%20button%20is%20clicked%20on%2C%20it%20returns%20an%20error.%26nbsp%3B%20On%20the%20quick%20access%20menu%2C%20that%20function%20is%20%22grayed%20out%22%20if%20no%20columns%20are%20filtered%20to%20prevent%20the%20user%20from%20creating%20the%20error.%26nbsp%3B%20Can%20I%20gray%20out%20a%20button%3F%3F%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-199920%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-202626%22%20slang%3D%22en-US%22%3ERe%3A%20Clear%20Filter%20with%20a%20button%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-202626%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20make%20the%20macro%20smart%20enough%20to%20look%20for%20the%20presence%20of%20a%20filter.%26nbsp%3B%20If%20you%20want%20to%20show%20all%20data%20then%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20DisplayEverything()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BIf%20ActiveSheet.AutoFilterMode%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20ActiveSheet.ShowAllData%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20remove%20filtering%20completely%20then%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20RemoveTheFilter()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BIf%20ActiveSheet.AutoFilterMode%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Cells.AutoFilter%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-200059%22%20slang%3D%22en-US%22%3ERe%3A%20Clear%20Filter%20with%20a%20button%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-200059%22%20slang%3D%22en-US%22%3E%3CP%3EKenneth-%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20have%20the%20code%20snippet%20you're%20using%3F%26nbsp%3B%20You%20probably%20just%20need%20to%20add%20in%20some%20Error%20Handling%20or%20incorporate%20this%20small%20portion%20of%20code%20at%20the%20top%20of%20the%20procedure%20that%20suppresses%20errors%20(Note%3A%20this%20statement%20should%20be%20used%20with%20caution)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EOn%20Error%20Resume%20Next%3C%2FPRE%3E%0A%3CP%3EHere%20is%20some%20more%20information%20on%20Error%20Handling%20by%20Chip%20Pearson%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.cpearson.com%2Fexcel%2Ferrorhandling.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EError%20Handling%20Info%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am only a "medium" excel guy, please be patient....

 

I have added a button to a spreadsheet that has columns that can be filtered.  I have assigned the "clear" function/macro to the button.  However, if no columns are filtered at the time that the button is clicked on, it returns an error.  On the quick access menu, that function is "grayed out" if no columns are filtered to prevent the user from creating the error.  Can I gray out a button???  

2 Replies

Kenneth-

 

Do you have the code snippet you're using?  You probably just need to add in some Error Handling or incorporate this small portion of code at the top of the procedure that suppresses errors (Note: this statement should be used with caution):

 

On Error Resume Next

Here is some more information on Error Handling by Chip Pearson:

 

Error Handling Info

 

You can make the macro smart enough to look for the presence of a filter.  If you want to show all data then:

 

Sub DisplayEverything()
   If ActiveSheet.AutoFilterMode Then
      ActiveSheet.ShowAllData
   End If
End Sub

 

If you want to remove filtering completely then:

 

Sub RemoveTheFilter()
   If ActiveSheet.AutoFilterMode Then
      Cells.AutoFilter
   End If
End Sub