Forum Discussion

diseydaly1983's avatar
diseydaly1983
Copper Contributor
May 13, 2020
Solved

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 manage...
Macros and VBA
  • bhushan_z's avatar
    bhushan_z
    May 13, 2020

    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
bhushan_z's avatar
bhushan_z
Iron Contributor
May 13, 2020

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

Resources