Macro for clearing filters on a protected sheet

New Contributor

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

@StephanieDowney 

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 Sub

 

 It's just a bit the other way around... cancel all filters when you close it :).

 

NikolinoDE

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?

@StephanieDowney 

Here is the code when opening the file.