VBA - Show All Data

[Foz1980]

Hi all,

I am using the below code to show all data every time a specific workbook is opened, however if I protect the sheet and share it then it does not work.
When I've protected it I have ticked the box to allow the use of Auto-Filter but the code does not work.

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

Can anyone help please?

 

[Tim_Excel_]

Have the users enabled macros?

Alternatively you could try to clear all filters when the user closes the WB.

 

[Foz1980]

Sorry, I should have been more specific with how it doesn't work.
All macros are enabled. But when opening the workbook, if a filter is present I get "Run-time error '1004'. ShowAllData method of Worksheet class failed".

 

[Tim_Excel_]

Have you tried the following?

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

 

[Foz1980]

Great, that's worked - thanks for that.

 

[Tim_Excel_]

Glad to have helped