May 13 2020 05:23 AM
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.
May 13 2020 06:44 AM
SolutionPrivate 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
May 13 2020 06:53 AM
Aug 18 2020 09:27 AM
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
May 13 2020 06:44 AM
SolutionPrivate 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