Jun 01 2018
05:39 AM
- last edited on
Jul 25 2018
10:03 AM
by
TechCommunityAP
Jun 01 2018
05:39 AM
- last edited on
Jul 25 2018
10:03 AM
by
TechCommunityAP
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???
Jun 01 2018 12:29 PM
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:
Jun 09 2018 08:28 PM
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