Protected Sheets - Show All Data

[Foz1980]

Hi All,

I use the below code for a few workbooks that my department uses, this unfilters the worksheets on opening. This was needed as some shared users were being annoying and saving the documents with columns filtered.

Private Sub Workbook_Open()
    Dim sh As Variant
    For Each sh In Worksheets
      If sh.FilterMode Then sh.ShowAllData
    Next
End Sub

However, I now also need to apply this to a shared and protected workbook.
I have tried adding ActiveSheet.Unprotect at the start and ActiveSheet.Protect at the end to remove the protection but it isn't working.

Can anyone help please?

 

[mole999]

did the person protecting it use a password

also activesheet might be the issue if it is not working where you expect it to

 

[Foz1980]

did the person protecting it use a password

also activesheet might be the issue if it is not working where you expect it to

Yes it is password protected. I know the password, just don't know the code needed to get to the point where a password can be used.

 

[mole999]

> https://www.mrexcel.com/forum/excel-questions/609996-vba-protect-sheet-password.html item2

 

[Foz1980]

> https://www.mrexcel.com/forum/excel-questions/609996-vba-protect-sheet-password.html item2

Cheers for this. But it's still not 100% fit for purpose - it seems to be breaking down when trying to unprotect the workbook as it is also shared. I don't want it to unshare the workbook as that would remove users from being able to save any changes they have made, but I don't know if I can bypass that part?

 

[mole999]

sorry, I missed the "SHARED" excel part, I don't know a way to circumvent (I guess it is possible somehow)