MS Excel 2003 - Autofilter for protected cells




I have 3 cells A1, B1, and C1 that are protected and these are column
headings. I want to be able to autofilter the data under these columns using
auto filter. I locked these cells by selecting format->protection->locked.
I then selected protect this sheet and allow user to do autofilter and to
select unlock and locked cells. However, when I try to use autofilter for
these locked cells I still can't use autofilter. I also tried selecting
autofilter on these columns first and then protecting them but then I can not
use autofilter on the other columns that are not protected. Can you please
explain how I can make sure autofilter work on all cells of the sheet
including the locked cells?




Dave Peterson

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question