Forum Discussion
Macro for clearing filters on a protected sheet
I have an Excel file that I need to clear the filters from a column on a password protected sheet.
When someone opens the shared file (from OneSite), I would like for the filters in 2 of the columns cleared but need for the sheet to stay protected as it has been messed up by others and takes some work to fix.
I am ok if the protection is cleared then the filters cleared and the protection put back with the password.
Can someone write me a code to do this?
Thank you
3 Replies
Here's a little example with VBA code and file...hope it helps 🙂
Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.ActiveSheet.Unprotect ("1234") If ActiveSheet.AutoFilterMode Then If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=0 ActiveWorkbook.ActiveSheet.Protect ("1234") End SubIt's just a bit the other way around... cancel all filters when you close it :).
- Thank you so much for your help on this and it is so close to what I need but it unclicks the auto filter when the protection is put back but I need the auto filter to be on when the file opens. Can you add that to this for me?
Here is the code when opening the file.
