Enable autofilter in protected worksheets in Excel 2000

[dazzag82]

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.

 

[Arvi Laanemets]

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]

Thanks for that thought it did not seem to work. Your code enables the
autofilter when the file is opnened. Lets say a user has the file open, makes
changes to the data and then hits one of the command buttons to do a date
sort, the macro will unprotect the sheet, do the date sort and then reprotect
it again. I need to have the autofilter available at all times, especially if
data has been modified.

Can you help?

 

[Ron de Bruin]

See
http://www.contextures.com/xlautofilter03.html#Protect

 

[Arvi Laanemets]

Hi

The autofilter remains allowed until end of session.
1. User opens the workbook - the Open event is fired, it removes the
password, enables autofilter, and protects the sheet again;
2. The user does whatever he/she does - autofilter feature for protected
sheet works normally through entire session;
3. The user closes workbook - autofilter settings aren't saved along with
it, so next for next session they must be reset again.

Arvi Laanemets

 

[Arvi Laanemets]

NB! You have to create an Open event for workbook, not some procedure in
standard module!

When you open VBA editor, in VBA Project window locate YourProject>Microsoft
Excel Objects>ThisWorkbook, right-click on it, and select View Code.
At top of code window are 2 dropdowns - from left one select Workbook, and a
dummy Open event is created. Edit it.


Arvi Laanemets

 

[dazzag82]

Thanks Arvi

I had to modify some of my other existing macros and I got it to work
perfectly. You have saved me thousands of dollars in people not needing an
upgrade. Thank You so much!