SOLVED

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

Copper 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 (Copper 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 

1 best response

Accepted Solutions
best response confirmed by diseydaly1983 (Copper 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

View solution in original post