Hi

You have to write a workbook Open event, which enables autofilter for this
sheet every time the workbook is opened. Like this:

Private Sub Workbook_Open()
Sheets("MySheet1").Unprotect Password:="MyPassword"
Sheets("MySheet1").EnableAutoFilter = True
Sheets("MySheet1").Protect Password:="MyPassword",
UserInterfaceOnly:=True
End Sub

To avoid that user reads you password from VBA code, you may protect your
VBA project with password too.



Arvi Laanemets



"dazzag82" wrote in message
...
I have excel 2003 and have a created a spreadhseet for multipole users to
access. The format of the spreadhseet, data validations and formulas mean
that the worksheets need to be protected, which I have done with a
password.
The protection does allow the use of autofilter. I did this by ticking the
appropriate check box in the Protect Sheet dialogue box.

The issue is that I have command buttons with macros to do certain tasks
such as sort data ranges by date and display various Custom Views. In
order
for the macros to work each macro VBA code follows a 3 step rule.

1. Unprotect the relevant worksheet(s)
2. Perform task, e.g. display custom view
3. Reprotect the relevant worksheets and allow filtering.

The macros all work fine on my machine but if someone else who has excel
2000 clicks the command buttons the macros do not work. The debugging
shows
that the issue is related to the use of autofilter in protected
worksheets.
My research so far has led me to believe that the only solution to this is
that an upgrade to 2003 is required.

An example of the code I have to display a custom view is as follows

For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:="putpasswordhere"
Next

ActiveWorkbook.CustomViews("View1").Show

For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Protect Password:="putpasswordhere",
DrawingObjects:=True,
Contents:=True, Scenarios:=True, AllowFiltering:=True
Next

The "AllowFiltering:=True" text only seems to be understood in excel 2003.
Is there anhything I can use in my code which will work for 2000. The use
of
autofilter is a neccessity and when clicking the comman buttons, the
macros
must work so that after it has ran, the sheets have been reprotected again
and the use of autofilter is enabled. Please advise if VBA code can do
this
or if I need an upgrade to 2003 for other users.