AutoFilter settings in shared workbook

[jsurkin]

I'm using a shared, protected workbook, with AutoFilter enabled, that
allows users to make edits in certain columns. When users edit the
workbook and save it, their AutoFilter settings are being saved as
well, even when the Filter settings box is unchecked in the Share
Workbook dialog box. When another user opens the workbook, the first
user's AutoFilter settings are displayed. I would like to have the
workbook default to show all data when it is opened, regardless of the
AutoFilter settings when it was last saved.

I tried using the following macro:

Option Explicit
Sub auto_Open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
With wks
If .FilterMode Then
.ShowAllData
End If
End With
Next wks
End Sub

The macro works perfectly when the workbook is unshared and
unprotected. It also works when the workbook is shared and
unprotected.

However, when I protect the workbook, I get the following error:

Run-time error '1004'
Method 'ShowAllData' of object '_Worksheet' failed

Is there any way to make the macro run without unprotecting the
workbook? Or is there another possible solution?

Thanks!

--Jill

 

[Dave Peterson]

It's not a problem with the workbook protection--it's a problem with worksheet
protection.

You're going to have make a choice:
allow the filtering, but lose the worksheet protection
or
keep the worksheet protection, but lose the ability to filter.