protect cell ranges in spreadsheet and have data filters on

[Guest]

I have a need to protect cell ranges in a spreadsheet and have data filters
on and working. I am unable to both protect the worksheet with selected
unlocked cells and have data filters working.

 

[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
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Roger Govier]

Hi

Include something like the following code in your Workbook_Open sub

Private Sub Workbook_Open()
Worksheets("Sheet1").Select
With ActiveSheet
.EnableAutoFilter = True
.Protect Password:="password", DrawingObjects:=True, _
contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End With
End Sub


Change "Sheet1" to whatever your sheet name is and change "password" to
the password you want to use.