Protected sheet with filters

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

Dear All,

I am having to protect a work sheet so that only certain
cells can be changed.

Is there a way where I can protect a sheet but the
filters in the sheet still work?

When I protect the sheet and unlock the cells where the
filter is it still doesnt work. Any ideas?

Many Thanks

Adam
 
If you protect the sheet in code, you can allow this:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

(The worksheet has to have the dropdown arrows already applied.)

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

(you could use the workbook_open even under ThisWorkbook, too.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
How does one protect the sheet in code?

-----Original Message-----
If you protect the sheet in code, you can allow this:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

(The worksheet has to have the dropdown arrows already applied.)

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

(you could use the workbook_open even under ThisWorkbook, too.)

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


--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Try recording a macro when you do it manually.

Tools|macros|record new macro.

The Tools|protection|protect sheet.

You'll get some lines of code that look a lot like the previous message.
 
Back
Top