MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using Autofilter when spreadsheet is protected

Posted by Lisa Gilbert on November 24, 2001 8:39 AM

In Excel 2000 I have a database spreadsheet that uses the autofilter tool. I want to lock some cells that either have formulas in or cells that I don't want others to input data into, or delete the formulas. When I lock the cells and protect the worksheet, the autofilter tool cannot be used. I have tried to specify data entries by using data validation, and this works to some extent, but unfortunately other users can still delete the formulas.

Could you let me know if it is possible to do this??

Posted by John on November 24, 2001 9:46 AM

This code will protect the sheet but allow the use of the auto filter:

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

Posted by Lisa on November 25, 2001 2:22 AM

Thanks John - but I still want to be able to input in some of the columns, just not the ones with formulas. Any ideas?

PS could you elaborate on the code you gave me - like where do I put it!!