Filtering a protected WS in a shared workbook - Excel 2000

Hi,

I am having problem with filtering a protected worksheet in a shared
workbook. I used the macro below which was provided on this discussion
group. When I share the workbook and open the file again, I get the
following error:

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

Your help is greatly appreciated!
Phuong

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Data")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

I think you've got to make a choice.

If the workbook is shared, then you can't change the protection of any
worksheet.

You can't even change it to allow the code to do the changes
(Userinterfaceonly:=true).

So you can unshare it and leave the worksheet protection.

or...

You can unprotect the worksheet and leave the workbook shared.



Phuong Nguyen wrote:
>
> Hi,
>
> I am having problem with filtering a protected worksheet in a shared
> workbook. I used the macro below which was provided on this discussion
> group. When I share the workbook and open the file again, I get the
> following error:
>
> Run-time error '1004':
> Protect method of Worksheet class failed
>
> Your help is greatly appreciated!
> Phuong
>
> Private Sub Workbook_Open()
> 'check for filter, turn on if none exists
> With Worksheets("Data")
> If Not .AutoFilterMode Then
> .Range("A1").AutoFilter
> End If
> .EnableAutoFilter = True
> .Protect Password:="password", _
> Contents:=True, UserInterfaceOnly:=True
> End With
> End Sub

--

Dave Peterson

I am creating this shared workbook with multiple tabs for different groups to
fill in their data. The master worksheet contains links to other worksheets
and I'd like them to have the ability to filter but not edit (they must go to
their own worksheet to edit). Given this scenario, is there a way to protect
the master worksheet and allow filtering before sharing the workbook?

Thanks,
Phuong

"Dave Peterson" wrote:

> I think you've got to make a choice.
>
> If the workbook is shared, then you can't change the protection of any
> worksheet.
>
> You can't even change it to allow the code to do the changes
> (Userinterfaceonly:=true).
>
> So you can unshare it and leave the worksheet protection.
>
> or...
>
> You can unprotect the worksheet and leave the workbook shared.
>
>
>
> Phuong Nguyen wrote:
> >
> > Hi,
> >
> > I am having problem with filtering a protected worksheet in a shared
> > workbook. I used the macro below which was provided on this discussion
> > group. When I share the workbook and open the file again, I get the
> > following error:
> >
> > Run-time error '1004':
> > Protect method of Worksheet class failed
> >
> > Your help is greatly appreciated!
> > Phuong
> >
> > Private Sub Workbook_Open()
> > 'check for filter, turn on if none exists
> > With Worksheets("Data")
> > If Not .AutoFilterMode Then
> > .Range("A1").AutoFilter
> > End If
> > .EnableAutoFilter = True
> > .Protect Password:="password", _
> > Contents:=True, UserInterfaceOnly:=True
> > End With
> > End Sub
>
> --
>
> Dave Peterson
>

Not that I know.


Phuong Nguyen wrote:
>
> I am creating this shared workbook with multiple tabs for different groups to
> fill in their data. The master worksheet contains links to other worksheets
> and I'd like them to have the ability to filter but not edit (they must go to
> their own worksheet to edit). Given this scenario, is there a way to protect
> the master worksheet and allow filtering before sharing the workbook?
>
> Thanks,
> Phuong
>
> "Dave Peterson" wrote:
>
> > I think you've got to make a choice.
> >
> > If the workbook is shared, then you can't change the protection of any
> > worksheet.
> >
> > You can't even change it to allow the code to do the changes
> > (Userinterfaceonly:=true).
> >
> > So you can unshare it and leave the worksheet protection.
> >
> > or...
> >
> > You can unprotect the worksheet and leave the workbook shared.
> >
> >
> >
> > Phuong Nguyen wrote:
> > >
> > > Hi,
> > >
> > > I am having problem with filtering a protected worksheet in a shared
> > > workbook. I used the macro below which was provided on this discussion
> > > group. When I share the workbook and open the file again, I get the
> > > following error:
> > >
> > > Run-time error '1004':
> > > Protect method of Worksheet class failed
> > >
> > > Your help is greatly appreciated!
> > > Phuong
> > >
> > > Private Sub Workbook_Open()
> > > 'check for filter, turn on if none exists
> > > With Worksheets("Data")
> > > If Not .AutoFilterMode Then
> > > .Range("A1").AutoFilter
> > > End If
> > > .EnableAutoFilter = True
> > > .Protect Password:="password", _
> > > Contents:=True, UserInterfaceOnly:=True
> > > End With
> > > End Sub
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson