MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using Autofilter when Spreadsheet is Protected

Posted by Lisa on November 26, 2001 6:08 AM

In Excel 2000 I have a database spreadsheet that uses the autofilter tool, but other users will also need to input data. I want to lock some cells that have formulas in so that other users can't 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.

I had a response from John (see below) but I am not sure where (or how) to put the code he gave me! Also I think this code will protect the whole spreadsheet rather than just the columns I want it to. Anyones ideas will be gratefully received and appreciated!

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 Paul on November 26, 2001 7:34 AM

Right click on the sheet tab, view code and paste the code there, it should not change what cells you have protected and unprotected