SOLVED

VBA.... clear filters & protect sheet (but allow auto filters) upon closing

%3CLINGO-SUB%20id%3D%22lingo-sub-1386192%22%20slang%3D%22en-US%22%3EVBA....%20clear%20filters%20%26amp%3B%20protect%20sheet%20(but%20allow%20auto%20filters)%20upon%20closing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386192%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20There%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20creating%20a%20leave%20planner%20for%20my%20team%20and%20when%20someone%20closes%20it%2C%20it%20want%20it%20to%20clear%20any%20filters%20and%26nbsp%3Bprotect%20the%20document%20(without%20a%20password)%20but%20allow%20auto%20filtering.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20managed%20to%20get%20the%20clear%20filters%20to%20work%20by%20using%20this%2C%20but%20when%20I%20try%20and%20add%20any%20code%20to%20protect%20the%20sheet%20and%20allow%20filters%2C%20its%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20great%20with%20excel%20so%20please%20bare%20with%20me.%20I%20know%20these%20things%20can%20be%20done%20but%20I%20know%20nothing%20about%20code!%20I%20literally%20google%20everything.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Workbook_Open()%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20ws%20As%20Worksheet%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20Each%20ws%20In%20Worksheets%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20ws.AutoFilterMode%20Then%20ws.AutoFilter.ShowAllData%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%20ws%3CBR%20%2F%3E%26nbsp%3BEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20can%20help%2C%20that%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1386192%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386440%22%20slang%3D%22en-US%22%3ERe%3A%20VBA....%20clear%20filters%20%26amp%3B%20protect%20sheet%20(but%20allow%20auto%20filters)%20upon%20closing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F660172%22%20target%3D%22_blank%22%3E%40diseydaly1983%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Workbook_Open()%0A%20%20%20%20%20Dim%20ws%20As%20Worksheet%0A%20%20%20%20%20For%20Each%20ws%20In%20Worksheets%0A%20%20%20%20%20%20%20%20If%20ws.AutoFilterMode%20Then%20ws.AutoFilter.ShowAllData%0A%20%20%20%20%20%20%20%20ActiveSheet.Protect%20DrawingObjects%3A%3DTrue%2C%20Contents%3A%3DTrue%2C%20Scenarios%3A%3DTrue%20_%0A%20%20%20%20%20%20%20%20%2C%20AllowFiltering%3A%3DTrue%0A%20%20%20%20%20Next%20ws%0A%20End%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1386478%22%20slang%3D%22en-US%22%3ERe%3A%20VBA....%20clear%20filters%20%26amp%3B%20protect%20sheet%20(but%20allow%20auto%20filters)%20upon%20closing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1386478%22%20slang%3D%22en-US%22%3E%3CP%3EAMAZING%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662966%22%20target%3D%22_blank%22%3E%40bhushan_z%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHANK%20YOU%20SO%20MUCH!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi There

 

I am creating a leave planner for my team and when someone closes it, it want it to clear any filters and protect the document (without a password) but allow auto filtering.

 

I managed to get the clear filters to work by using this, but when I try and add any code to protect the sheet and allow filters, its not working.

 

I am not great with excel so please bare with me. I know these things can be done but I know nothing about code! I literally google everything.

 

 

Private Sub Workbook_Open()
     Dim ws As Worksheet
     For Each ws In Worksheets
         If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
     Next ws
 End Sub

 

If anyone can help, that would be appreciated.

3 Replies
Best Response confirmed by diseydaly1983 (Occasional Contributor)
Solution

@diseydaly1983 

Private Sub Workbook_Open()
     Dim ws As Worksheet
     For Each ws In Worksheets
        If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True
     Next ws
 End Sub

What would the code be if you need it do this but, add have to enter a password to protect the sheet when closing TIA! @bhushan_z