Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

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??

Check out our Excel VBA Resources

Re: Using Autofilter when spreadsheet is protected

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

Re: Using Autofilter when spreadsheet is protected

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!!

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.