Shared workbook, Protection and AutoFilters

[Hugh]

Using Excel 2002 on XP, I have created a workbook which I use to
collect financial submissions in a database format. The spreadsheet
uses AutoFilters to suppress lines in the database such that only the
lines relevant to a particular respondent are displayed.

I can protect the workbook sheets, protect and share the workbook and
then operate the AutoFilters manually, but I cannot do the same and
then reliably operate the AutoFilters by macro.

It will work after doing the above whilst I am still in the workbook.
But if I close and reopen it, I get ‘Run-time error 1004 You cannot
use this command on a protected sheet etc…"

The code I use to protect the sheet is:

Sub Protect()
Sheets("Sheet1").Protect Contents:=True, UserInterfaceOnly:=True _
, AllowFiltering:=True
End Sub

The code I am trying to use to operate the AutoFilters is:

Sub Select()
Selection.AutoFilter Field:=2, Criteria1:="Selection"
End Sub

Can anybody suggest alternative code to operate the filters?

Thanks,
Hugh

 

[Dave Peterson]

I'm not sure there is an alternative--well, remove the worksheet protection.

If you share a workbook, you can't change the protection of a worksheet.

There's lots of stuff that can't be used in a shared workbook.

 

[Hugh]

Any advance on no?

I know this question has beeen raised before (see link below), but
surely someone, somewhere must have a work-around by now?

http://groups.google.co.uk/groups?h...=1&selm=#[email protected]

Cheers,
Hugh

 

[Dave Peterson]

That code changes the protection of the worksheet. Since you can't change
worksheet protection in a shared workbook, it doesn't apply.