Excel: Using Auto-Filter on Protected Sheet
Posted on 2003-03-03
I want to protect a worksheet but still allow AutoFilter on a data list. (Yes, I too would rather do this in Access, but my audience knows Excel, so . . . here's my question.)
I'm creating a workbook template that includes quite a bit of VBA automation as well as some pretty complex formulas. I want to protect the sheets, except for the cells that users will require for data entry. I'm having trouble with one sheet, however, because I also want to allow the users to Auto-Filter the list of data they've entered.
Here are the specifics: Users enter expense items in a prescribed range (A11:G1000) with column headings as follows: (A)Category, (B)Date, (C)Billable, (D)Cost, (E)Total [calculated field], (F)PO #, (G)Description. In addition to the calculated column E, there are other fields on the sheet with complex formulas, and a couple of buttons that trigger VBA procedures.
I want to protect the sheet but still allow users to filter their data (all Travel expenses, for instance, or expenses between 2/1/2003 and 2/28/2003, or whatever they might think of). Since I can't anticipate all of the questions they might have, Auto-Filters seem like the perfect solution, EXCEPT that the Auto-Filters stop working once I protect the sheet.
Is there a way to get the best of both worlds? The only option I thought of was to add a button "Click here to AutoFilter" and create a procedure that would test for protection, unprotect if the sheet is protected, add the AutoFilter headings, and change the button's caption to "End AutoFilter." If the sheet is already unprotected, the procedure would remove AutoFilter and re-protect the sheet. That's a little too manual for my taste, though, and I'd like to find something more elegant. Any ideas?