Sharing a spreadsheet with Auto FIlter, and mixed Protection

[Levite]

I have a user that has a spreadsheet she wants people to be able to filter on and also have some (not all) the cells protected.

I found a some good advice on using a Macro on the Workbook open event to accomplish this:

Private Sub Workbook_Open()
With ActiveWorkbook.Sheets("Sheet1&quot
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With
End Sub

However she now wants the sheet "Shared". And when the sharing is enabled and the worksheet is opened we get the following error:

Run time error '1004':
Protect method of Worksheet class failed.


Any ideas?

 

[xlbo]

I think you need to remove the Contents:=True bit - userinterfaceonly should do the job on its own....
BTW - I have run into MANY problems with functionality on shared workbooks - IMHO, any shared workbook needs to be incredibly simple if you don't want it to fall over at some point Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Levite]

Removed Contents :=True and still get same error.

This is what the event code looks like now:

Private Sub Workbook_Open()
With ActiveWorkbook.Sheets("Sheet1&quot
.EnableAutoFilter = True
.Protect userInterfaceOnly:=True
End With
End Sub

?

 

[xlbo]

Sorry - didn't read the "shared" bit - you just cannot do this on a shared workbook - protection must be set BEFORE a workbook is shared

You could try using the "exclusive access" method before the protection is set but this dumps everyone else out of the workbook and therefore probably isn't usable for you as this would happen every time someone opens the workbook

As I said, shared workbooks need to be very very simple - there's just too much you can't do with them Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde