How? - Error message for turning off autofilter with code

R

ryssa

I have a button on a worksheet to 'Show All Records'.

There are two ways i found to do it, ie:

ActiveSheet.ShowAllData

and

If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If

How do I display a message box saying "All records shown" if the use
clicks the button when the records are not filtered?

I found that the second way erases the filter arrows. I would like th
filter arrows at the top to remain.

Would appreciate anyones help, thanks
 
B

BrianB

This is one of those macros that I have been meaning to get round to an
not found the necessary motivation. It now resides in my personal.xls.

To check if a filter has been set beforehand it would be necessary t
run the same loop, so there is no point. This will work in any case :-


Code
-------------------

'------------------------------------------------
Sub AUTOFILTER_RESET()
Dim MyFilter As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set MyFilter = ActiveSheet.AutoFilter.Range
cols = MyFilter.Columns.Count
'-----------------------------------
For c = 1 To cols
MyFilter.AutoFilter field:=c
Next
'-----------------------------------
Application.ScreenUpdating = True
MsgBox ("All data visible.")
Application.Calculation = xlCalculationAutomatic
End Sub
'-------------------------------------------------
 
Top