VBA code - filter reset on closure

[Rebro88]

Hello

After numerous google searches and failed attempts. I'm giving in and asking for advice as my VBA knowledge is very little.

I have a workbook that has protected cells where my colleagues report actions. The filters are turned on so they can filter the actions based on department, shift etc.

Is there a VBA code that will reset any filters applied and not remove them?

The codes I have tried through my google searches remove the filters which then requires the sheet to be unlocked and the filter added again.

Thanks in advance
Rebro88

 

[rlv01]

Do you mean this?

ActiveSheet.ShowAllData

 

[SQUIDD]

still think with the above, you will need to unprotect the workbook.

but this can be coded too

 

[SQUIDD]

Something like the below example

on workbook close

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Sheet1").Unprotect "Password"
ActiveSheet.ShowAllData
Worksheets("Sheet1").protect "Password"
End Sub

 

[Rebro88]

Something like the below example

on workbook close

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Sheet1").Unprotect "Password"
ActiveSheet.ShowAllData
Worksheets("Sheet1").protect "Password"
End Sub

Thanks for the quick replies.

This is the closest its has been to working.

The only issue is when it unprotects and protects it takes away the protect sheet option autofilter and format rows.

Is there a way around this?

 

[SQUIDD]

Hi, sorry for the late reply

play with the true and false in the below. until you get your desired results

however, im suprised you didnt flag me an about a possible save notification on close.

see how you get on

Sub db()
strPword = "password"
Worksheets("Sheet1").Unprotect strPword
ActiveSheet.ShowAllData
Worksheets("Sheet1").Protect Password:=strPword, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True, AllowFormattingCells:=False, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=False, AllowInsertingRows:=False, _
AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, AllowDeletingRows:=False, _
AllowSorting:=False, AllowFiltering:=True, AllowUsingPivotTables:=False
End Sub

 

[rlv01]

Is there a way around this?

As @SQUIDD notes in his post, the Worksheets("Sheet1").Protect command has a lot of options/properties available to you, so you can configure the sheet protection as you like.