How do I use AutoFilter when Protection is on?

Posted by Donald A Barron on June 27, 2001 12:24 PM

I am stuck. I have no idea how to use Macros or VBasic or Script Editor, or any of that. My question is, can I use the AutoFilter drop down box when Protection is on? I know I can uncheck contents and that will work but I am trying to get around that for if someone accidentally hits "delete" then the cell is lost if the person does not know what they are doing. I figured out how to use validate to restrict new data to be inputted into the area but not how to restrict it from the delete command. Can anyone help?

Thanks :)

Donald

Posted by Connie on June 28, 2001 6:07 AM

Donald,
You WILL need VBA in order to sort a protected sheet. Excel 2002 provides this capability (from what I've read) but users of older versions are stuck. The following is an answer to a similar question posted here 5/29/01 by Dave Hawley (msg 18573):
This is the VBA code:
Sub SortAndProtect()
ActiveSheet.Unprotect Password:="secret"
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes,_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect Password:="secret"
End Sub

This assumes that the first column you want to sort by is in A2 (assuming header in A1).
Hope this helps. If it doesn't, you might want to explore some other Excel functions which simulate sorts such as RANK, which I've had luck with. If you're trying to sort nominal data, perhaps you can figure out a way to assign numbers to them, and then you can use RANK or LARGE functions to sort them.
Good luck,
Connie

Posted by Donald A Barron on June 28, 2001 8:48 AM

Connie,

Thank you for pointing me in the right direction. I used help in VBA and it gave me this command:

Sub EnableAutoFilter()

ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True

End Sub

When I applied it, it worked. So, as an FYI, I will do a new post for everyone to know. Thanks for your help :)

Donald