Autofilter in protected workbook not working

[Guest]

Trying to use Autofilter in protected workbook for excel 2000. Used the
following code.

Private Sub Workbook_Open()
Build.Protect password:="AbCd13579", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
Build.EnableAutoFilter = True
End Sub

But getting a runtime error 424 with the message as 'Object Required'.

Steps I followed are -

1. Entered VB code, mentioned above, in 'thisworkbook' in VB editor.
2. Protect workbook with my own options and with password.
3. Tools --> Share and Protect workbook

Then I am getting the runtime error while opening the excel again.

Is there any workaround for this?

 

[Shailesh Shah]

When the sheet object(in your case Build) is not found, you get a
runtime error 424 with the message as 'Object Required'.

Check it is a codename or name of worksheet.


Regards,
Shah Shailesh
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)

 

[Guest]

'Build' is the name of one worksheet. Checked everything. Could not find
source of problem yet.

 

[Guest]

Go to the VBE and in the properties window of the "Build" worksheet and under
the Name field assign a "Code Name" to the worksheet as "Build". This will
work now. Otherwise the code you have entered in the Workbook_Open will ask
you for an Object .... Worksheets("Build") .... which you have not defined

Regards
SGL

 

[Dave Peterson]

The bad news is that if the workbook is shared, then you can't change the
protection of a worksheet--even just to add userinterfaceonly:=true.

(after you resolved if "Build" was the code name or should have been
Worksheets("build").)

 

[Guest]

Thanks. Thats right. I tried later by removing shared option. At that time
the VB code didn't return any error.

So, is it correct that 'Autofilter cannot be used in protected and shared
workbook in excel 2000'?
Is there any workaround for this?

 

[Dave Peterson]

Maybe you could add another worksheet that is nothing but formulas pointing back
to the protected worksheet.

Leave that worksheet unprotected and allow filtering there.

=if('sheet 99'!a1="","",'sheet 99'!a1)

Drag down and across as far as you need.