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

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.

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

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

Forum Discussion

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

3 Replies

Resources