Auto-Filter Not Working With Protection Turned On

[iwgunter]

I am using columns A-L with content. I want certain cols to be protected
as they bring inthe dynamic content.

Firstly, I selected the whole worksheet [ctrl+a], right clicked and
took the tick out of Locked, I then highlighted the whole of cols G, H,
I & K by clicking on the column letter and checked the Locked box. I
then highlighted the whole of row 1 as this is the column headers and I
have auto-filter running and unchecked the Locked box. Then I applied
the protection so the cells can't be changed.

But now the auto-filter won't work. Is this a known problem with
Excel?

I'm using Excel 2000 [9.0.2720]

Ta
Ian

 

[Roger Govier]

Hi

You will need to put protection on, with the userinterface still set to
True.
Enter some code like the following into a standard module of your
Workbook to invoke the Protection.
Change the "mypassword" to whatever you want.

Sub Protectsheet()
With ActiveSheet
.EnableAutoFilter = True
.Protect Password:="mypassword", DrawingObjects:=True, _
contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End With
End Sub

Sub UnProtectSheet()
With ActiveSheet
.Unprotect Password:="mypassword"
End With
End Sub

You can copy the code I posted and paste it into your Visual Basic
Editor
(VBE) in a Standard Module located in your project (workbook). Shortcut
keys would be ..

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select Workbook on left
If no modules exist:
Insert | Module
Paste code in Module
If modules exist:
Double click desired module
Paste code in Module
Add further code if requiredClick on the Excel icon at top left of the
VB Editor to return to the Worksheet.

I usually add shortcut keys to the macros for my own use.
Tools>Macro>Macros>Select Protectsheet>Options and enter a letter code
in the box
Repeat for the Unprotect macro.

Then you can quickly switch protection on and off for yourself with
these shortcuts.

 

[Dave Peterson]

If you already have the outline/subtotals 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

I am using columns A-L with content. I want certain cols to be protected
as they bring inthe dynamic content.

Firstly, I selected the whole worksheet [ctrl+a], right clicked and
took the tick out of Locked, I then highlighted the whole of cols G, H,
I & K by clicking on the column letter and checked the Locked box. I
then highlighted the whole of row 1 as this is the column headers and I
have auto-filter running and unchecked the Locked box. Then I applied
the protection so the cells can't be changed.

But now the auto-filter won't work. Is this a known problem with
Excel?

I'm using Excel 2000 [9.0.2720]

Ta
Ian