Excel 2002 Protect Sheet & Use AutoFilter Option

[turbotoan]

Has anyone come up with a way to code up this functionality to work in Excel 97?
Essentially, I need to protect a sheet, but allow AutoFilter to be used for viewing purposes. In Excel 97 this is not permitted (even if the columns you want to AutoFilter on are NOT locked...).

See this site for what it is supposed to do in Excel 2002 - "http://support.microsoft.com/default.aspx?scid=kb;EN-US;q289269"

I'll appreciate the help...

 

[Derek]

Hi Turbotoan

The only way around this I have found is to provide 2 macro buttons on a part of the sheet which will not get hidden when lines are filtered.
Name on Button 1 = "Filter by entry in D1"
Name on Button 2 = "Show all Data"
Macro1 Code:
On Error Resume Next
ActiveSheet.Unprotect("yourpassword")
Selection.AutoFilter Field:=4, Criteria1:=Range("D1").Value, Operator:=xlAnd
ActiveSheet.Protect("yourpassword")
End Sub

Note: Here I am using Field 4 and picking up the criterial value from cell D1 (change to suit your circumstances)

Macro2 Code:
On Error Resume Next
ActiveSheet.Unprotect("yourpassword")
ActiveSheet.ShowAllData
ActiveSheet.Protect("yourpassword")
End Sub

You will have to provide some instructions to get your user to type into D1 (or your chosen cell) the criteria to filter by. Or perhaps you could do it with an input box?

Good luck
Derek

 

[Guest]

Try this, I got this from Mr Excel (can't remember who) and it worked for me.

Sub protect1()
ActiveSheet.EnableAutoFilter = True
ActiveSheet.Protect Contents:=True, userInterfaceOnly:=True

End Sub

Lisa

 

[Derek]

That's neat Lisa, that will help me too, thanks
Derek

 

[turbotoan]

Thanks Lisa,
That works wonderfully!