Worksheet Protection not Working

[Glenn]

Hi,

When I protect a worksheet but allow all users of this worksheet to
UseAutoFilter, the Auto Filter feature is still locked out.

Any idea on how to remedy this?

Thank you

 

[Tom Hutchins]

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

Hope this helps,

Hutch

 

[Glenn]

Hutch,

I'm using excel 2003 and the dialog box appears when I select protect
worksheet asking which functions I want allow users to permission to use. I
check the box next to Use AutoFilter, but it still locks out that function
once the sheet is protected.

 

[Tom Hutchins]

You have to add the AutoFilter BEFORE protecting the worksheet. Are you doing
that?

Hutch

 

[Glenn]

Hutch,

I tried that and saw that it did work, but I was trying to avoid having the
drop down arrows showing all the time.

 

[Tom Hutchins]

All I can suggest then is to put a button your worksheet which will call a
macro like the following to toggle the AutoFilter on/off.

Sub ToggleAutoFilter()
Const Pwd = "xyz"
With ActiveSheet
'Unprotect the sheet
.Unprotect Password:=Pwd$
'Select the heading range
.Range("A1:E1").Select
'If AutoFilter is already applied
If .AutoFilterMode = True Then
'Remove the AutoFilter
.AutoFilterMode = False
Else
'Select the heading range and
'apply the AutoFilter
.Range("A1:E1").Select
Selection.AutoFilter
End If
'Reprotect the sheet
.Protect Password:=Pwd$, DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
AllowFiltering:=True
End With
End Sub

You would have to edit the password and headings range. You should also
password protect the VBAProject for the workbook, to keep users from seeing
the password in the macro. Excel is not terribly secure, however, once a
workbook is open.

Hope this helps,

Hutch

 

[Glenn]

Hutch,

Thank you for taking the time to help me with this. I will give it a try.