protect and use auto filter

Posted by John on July 09, 2001 11:52 AM

I would like to protect a sheet and still use the autofilter function in excel. I have a small invoice worksheet that when you enter the stock number of an item it looks up a table and enters the description and the price and totals and so forth. My problem is that there are about 100 products and I would like to view a list in order to find the correct stock number. Autofilter works out pretty well but it doesn't work when I protect the sheet to keep the users from changing the functions. Any Advice?

John

Posted by Ben O. on July 09, 2001 12:05 PM

I don't know of any way to use the autofilter on a protected sheet, but you make a macro that unprotects the sheet, applies the autofilter, then protects it again. If the workbook is password-protected, you could hardcode the password in the unprotect statement, then protect the VBA module so that no one can see what the password is.

-Ben

Posted by Peter on July 10, 2001 3:17 PM

Hi,

Try this, post it in VBA and run it via a buttton on the sheet.

Sub EnableAutoFilter()

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

End Sub

Hope it helps