Microsoft excel help

lee8040

The filters red to stay as an option to use so the msg tells them to reset the filters when trying the code to remove the filters it takes them away completely and the next person can't go in and use a filter to narrow down a search. The message will make them aware to reset the filters

neilmcl

So just to clarify, you don't actually want to remove the filters, as the code above will do, you simply want to reset them to show all data but leave the filter on?.

rmg1

The code above will remove any filter settings but leave the filters themselves (i.e. the little drop-downs) in place.
As for the message asking a user if they have removed the filters and stopping the save if they say no, that's easy enough to do. However, as mentioned above, why would you do it like that?

WaywardDriver

How about …

ask = MsgBox("Remove Filter?", vbYesNo)
If ask = vbYes Then
    For Each ws In ActiveWorkbook.Worksheets
        If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    Next
 End If

in both the WorkBook_Open and _BeforeClose subroutines.